From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELF LEFT OUTER JOIN = SELF JOIN including NULL values |
Date: | 2010-09-17 16:35:29 |
Message-ID: | 20100917163529.GD31536@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | bricklen | 2010-09-17 16:37:43 | PLPGSQL function to search function source for a list of terms |
Previous Message | Raymond O'Donnell | 2010-09-17 16:33:09 | Re: SELF LEFT OUTER JOIN = SELF JOIN including NULL values |