Skip to content

Postgresql work with null rows after joins

For example, we have next query:

select, 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 = left join C on C.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

  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

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)

User contributions licensed under: CC BY-SA
3 People found this is helpful