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?
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
NULL is not equal to NULL in Postgres, so your condition does what you want. This is the definition of NULL in SQL databases, so this applies to all databases. This is true regardless of whether the condition is in a where clause, on clause, case expression, or anywhere else.
If you wanted them to be equal, then you can use is not distinct from in the on clause:
on a.value is not distinct from b.value