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

On Wed, Apr 02, 2008 at 05:49:37PM +0100, Ian Sillitoe wrote:
> This is probably a stupid question that has a very quick answer, however it
> would be great if someone could put me out of my misery...
>
> I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
> function) where a joining column can be NULL

You can't, NULL is not a value like other values.

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

'transform_null_equals' won't help you at all here since it only help
in the very specific case of comparing with a constant. The easiest is
to think of NULL as meaning 'unknown'. Clearly you can't copare that
usefully with anything.

Perhaps you can use a marker like -1 to acheive the effect you want?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.