For example, we have next query:
select A.id, A.field > (case when A.cond1 then
(case when B.field is null then 0 else B.field end) else
(case when C.field is null then 0 else C.field end) end)
from A left join B on B.A_id = A.id left join C on C.A_id = A.id;
Is there way to simplify work with replacing null to 0?
Yes, with COALESCE:
COALESCE(column_that_might_be_null, 0)
It accepts multiple arguments and works left to right returning the first non null:
COALESCE(column_that_might_be_null, another_possible_null, third_maybe_null, 0)
Is the equivalent of
CASE
WHEN column_that_might_be_null IS NOT NULL THEN column_that_might_be_null
WHEN another_possible_null IS NOT NULL THEN another_possible_null
WHEN third_maybe_null IS NOT NULL THEN third_maybe_null
ELSE 0
END
And it is SQL standard so should work on all compliant databases
PG also supports ISNULL and IFNULL which work similarly but I don't usually recommend them over COALESCE because they don't exist in all databases/don't necessarily work equivalently and aren't as flexible because they only accept 2 arguments. For me this isn't enough to justify saving 2 characters.. (And if you forget about COALESCE, and you end up doing ISNULL(ISNULL(ISNULL(first, second), third), 0) the SQL is more messy)
