I have this table called people
| id | name | lastname |
|---|---|---|
| 1 | John | Smith |
| 2 | Robert | Williams |
| 3 | Peter | Walker |
if I run the query
select CASE WHEN id is null THEN '0' ELSE id END as id
from people
where id='2'
The result is: | id | ---- | 2
I want to display id as 0 when it is Null in the table, but when I run
select CASE WHEN id is null THEN '0' ELSE id END as id
from people
where id='4'
| id |
|---|
My expected result is: | id | ---- | 0
A top-level SQL query that doesn't match any rows will return zero rows, which is different from returning NULL. This makes sense, because it lets you distinguish between the result for SELECT email FROM users WHERE id=4 when there is no such user and the result when there is a user but their email is null.
However, a subquery that returns no rows will evaluate to NULL the way you expected. So you can rewrite your code like this:
SELECT COALESCE( (SELECT id FROM people WHERE id = '4'), 0 );
COALESCE(x,y) is shorthand for CASE WHEN x IS NULL THEN y ELSE x END. It's helpful in cases like this where the expression for x is long and you don't want to have to write it twice.
