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

Thanks for the reply - after a bit more poking around it seems that:

t1.col IS NOT DISTINCT FROM t2.col

should work - although I guess this means an upgrade from 8.1 to 8.3

Cheers,

Ian

On Wed, Apr 2, 2008 at 6:23 PM, Martijn van Oosterhout <kleptog(at)svana(dot)org>
wrote:

> 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.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFH88EBIB7bNG8LQkwRAh7CAJ9ffmMnyE/OeJrTepSaOURb2WSRhACeMYql
> tnrzLDVLyFfHhDqKiY02QOM=
> =dhZf
> -----END PGP SIGNATURE-----
>
>