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

Hello everybody out there using PostgreSQL,

After having read the official documentation and having done extensive web search, I'm wondering how to perform
somethinglike a SELF LEFT OUTER JOIN in PostgreSQL, i.e. a SELF JOIN on a table containing NULL values in one of the
columnsto join. 

Thanks in advance for your help,
Julia

Heute erleben, was morgen Trend wird - das kann man auf der IFA in Berlin. Oder auf arcor.de: Wir stellen Ihnen die
wichtigstenNews, Trends und Gadgets der IFA vor. Natürlich mit dabei: das brandneue IPTV-Angebot von Vodafone! Alles
rundum die Internationale Funkausstellung in Berlin finden Sie hier: http://www.arcor.de/rd/footer.ifa2010 

On 17/09/2010 17:16, 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.

Something like this? -

select....
from my_table a
left join my_table b on (a.my_column = b.my_column)
...

Would this do it?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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/