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

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

Richard
1#
Richard Published in 2018-01-12 14:01:13Z
 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?
Justinas Marozas
2#
Justinas Marozas Reply to 2018-01-12 14:16:07Z
 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 sruprises 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 FASLE end 
Gordon Linoff
3#
Gordon Linoff Reply to 2018-01-12 14:03:21Z
 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 
 You need to login account before you can post.
Processed in 0.331715 second(s) , Gzip On .