Why COALESCE() does not work?

Question

Here is my query:

select COALESCE(t1.col1, 0) from table1 t1 where table2.id = t1.col2

So, The output is sometimes empty. Why? I used of COALESCE() just because if the result is empty it puts 0 instead. How can I fix it?

Note: COALESCE() works in this query as well:

select COALESCE(sum(t1.col1), 0) from table1 t1 where table2.id = t1.col2

---

Edit: Actually I want to say: If there is no result (no row founded) returns 0.

Answer 1

If this is your query:

select COALESCE(t1.col1, 0)
from table1 t1
where id = 10;

Then it will not return any rows when there is no match. If you know you want one row, you can use aggregation:

select COALESCE(MAX(t1.col1), 0)
from table1 t1
where id = 10;

This is guaranteed to return one row and it won't be NULL. Another approach uses union all:

select t1.col1
from table1 t1
where id = 10
union all
select 0
from table1 t1
where not exists (select 1 from table1 where id = 10);

Or, another way:

select coalesce((select col1 from table1 where id = 10),
                0)

In this context, the lack of row is turned into a scalar NULL value, so COALESCE() can work on that.

As a note: COALESCE() works fine. You need to realize that it works on column values. It cannot conjure a row when none are being returned.