Re: SELF LEFT OUTER JOIN = SELF JOIN including NULL values - Mailing list pgsql-general

On Fri, Sep 17, 2010 at 06:16:44PM +0200, julia.jacobson@arcor.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/