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

["Ian Sillitoe" <ian.sillitoe@xxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxxxx> 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