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

Ah - thanks and apologies for not finding those previous discussions. Does
anyone else feel this might be useful as a point on the NULL section of the
FAQ (it certainly would have saved me an afternoon)?

Cheers,

Ian

On Wed, Apr 2, 2008 at 6:24 PM, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
wrote:

> 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
> >
> >
> Sounds like you might want something like:
>
> SELECT * FROM tablea INNER JOIN tableb ON (NOT tablea.id IS DISTINCT FROM
> tableb.tablea_id_fk);
>
> which can also be written as:
>
> SELECT * FROM tablea, tableb WHERE NOT tablea.id IS DISTINCT FROM
> tableb.tableid_id_fk ;
>
> There's been lots of recent discussion of IS DISTINCT FROM, which is why
> it comes straight to mind.
>
> If that's not what you meant (by NULL = NULL) then might you be looking
> for an OUTER JOIN ?
>
> --
> Craig Ringer
>