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/