Re: (FAQ?) JOIN condition - 'WHERE NULL = NULL'

On Wed, Apr 02, 2008 at 05:49:37PM +0100, Ian Sillitoe wrote:
> Unless I've missed something, the docs on
> http://www.postgresql.org/docs/8.1/static/functions-comparison.html seem to
> suggest that the concept is an example of bad programming and the workaround
> (of switching on the 'transform_null_equals' config) is a hack. Is this all
> true or did my logic just get screwed up at some point?

Nope, it's all true.

The problem you are having is that one NULL is not the same as (and is not
not the same as) another NULL. NULL values are not equivalent to any other
value, incuding other NULLs. This is why some database people don't like to
allow NULLs at all.

> Unless I've just
> missed something obvious, it seems useful to be able to join two tables
> based on a condition where they share a NULL column - is there another way
> of doing this?

. . .AND t1.column IS NULL AND t2.othercolumn IS NULL.

Alternatively, you can use coalesce and join on some value, like this:

. . .AND coalesce(t1.column, 0) = coalesce(t2.othercolumn,0);

This is a bit of a hack, and won't work in every case (if you don't have a
value that you know can't be in either table, you're out of luck).

A