CODEWITHSUNDEEP
sqlpostgresql
Richard
Richard

Reputation: 65510

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

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?

Upvotes: 8

Views: 13069

Answers (2)

Justinas Marozas
Justinas Marozas

Reputation: 2652

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

Upvotes: 20

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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

Upvotes: 7

Related Questions