Postgres: join if field is not null in both tables?

Question

I'd like to join two tables in Postgres, but only if the value is not NULL in both fields.

It appears to me that a query like this will actually behave like this already, without further conditionals:

SELECT * FROM aTable a JOIN bTable b on a.value=b.value;

This doesn't return rows from my database where both a.value and b.value are NULL.

However, I'm not totally confident I understand what's going on. Is this because NULL is not equal to NULL in Postgres?

Answer 1

NULL is a field property declaring absence of a value. For this reason nothing is equal to NULL, even NULL itself. If you want to join on NULL, you'll have to employ functions.

Few things you can try:

-- be sure `escape value` doesn't normally occur in the column to avoid surprises
on coalesce(a.value, 'escape value') = coalesce(b.value, 'escape value')

or

-- no need for escape values, but more difficult to read
on (a.value is null and b.value is null) or a.value = b.value

or

-- even more text, but intent is more clear (at least to me)
on case
    when a.value is null and b.value is null then TRUE
    when a.value = b.value then TRUE
    else FALSE
end