Re: SELF LEFT OUTER JOIN = SELF JOIN including NULL values

On Fri, Sep 17, 2010 at 06:16:44PM +0200, julia(dot)jacobson(at)arcor(dot)de wrote:
> Hello everybody out there using PostgreSQL,
>
> After having read the official documentation and having done extensive
> web search, I'm wondering how to perform something like a SELF LEFT
> OUTER JOIN in PostgreSQL, i.e. a SELF JOIN on a table containing NULL
> values in one of the columns to join.

I guess you're looking for the IS NOT DISTINCT FROM operator. I.e.

SELECT *
FROM foo a
INNER JOIN foo b ON (a.x = b.x AND a.y IS NOT DISTINCT FROM b.y);

If you want a truth table:

WITH x(v) AS (VALUES (1),(2),(NULL))
SELECT l.v, r.v, l.v = r.v AS equality,
l.v IS NOT DISTINCT FROM r.v AS isnotdistinctfrom
FROM x l, x r;

--
Sam http://samason.me.uk/