Having a mental block with (self) outer joins - Mailing list pgsql-sql

From Thomas Kellerer
Subject Having a mental block with (self) outer joins
Date
Msg-id fui5v8$pi9$1@ger.gmane.org
Whole thread Raw
Responses Re: Having a mental block with (self) outer joins  (hubert depesz lubaczewski <depesz@depesz.com>)
List pgsql-sql
Hi, 

I'm playing around with putting a hierarchy of items into the database. But for some reason I'm having a mental block
understandingthe following:
 

I have a table category with id and parent_id implementing the typical adjacency model.

To get the first two levels of the hierarchy I use:

SELECT t1.name as lev1, t2.name as lev2
FROM category t1
LEFT JOIN category t2 ON t2.parent = t1.id
WHERE t1.name = 'ROOT'
;

Now what I don't understand is that the root node (which has a NULL parent_id) is not selected. 

My understanding from the outer join is that it would return all items from the "left" tables regardless whether they
havea corresponding row in the "right" table. 
 

So given the data

name, id, parent_id
ROOT, 1, NULL
CHILD1, 2, 1
CHILD2, 3, 1

I would have expected the following result:

ROOT, NULL
ROOT, CHILD1
ROOT, CHILD2

but the row with (ROOT,NULL) is not returned. 

I'm sure I'm missing something very obvious, but what?

Thanks in advance 
Thomas



pgsql-sql by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Desc Commnad in pgsql?
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: Having a mental block with (self) outer joins