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

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

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
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')


-- 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


-- 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
Gordon Linoff
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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.331715 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO