Skip site navigation (1) Skip section navigation (2)

Re: LEFT OUTER JOIN question

From: "Aaron Bono" <postgresql(at)aranya(dot)com>
To: seiliki(at)so-net(dot)net(dot)tw
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: LEFT OUTER JOIN question
Date: 2008-05-04 16:46:17
Message-ID: bf05e51c0805040946pad51696sb87bd341ff838b22@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Sun, May 4, 2008 at 11:28 AM, seiliki <seiliki(at)so-net(dot)net(dot)tw> wrote:

> Hi!
>
> I expect the SELECT to return two rows. Would some kind
> soul explain for me why it gives only one row?
>
> TIA
>
> CN
> =============
> CREATE TABLE x(c1 text,c2 int2);
> INSERT INTO x VALUES('a',10);
> INSERT INTO x VALUES('b',NULL);
>
> CREATE TABLE y(c1 int2,c2 int2,c3 text);
> INSERT INTO y VALUES(10,9,'yyy');
>
> CREATE TABLE z(c1 text,c2 text);
> INSERT INTO z VALUES('a','zzz');
> INSERT INTO z VALUES('b','zzzz');
>
> SELECT x.c1,y.c3,z.c2
> FROM x JOIN z USING (c1)
>  LEFT OUTER JOIN y ON (x.c2=y.c1)
> WHERE y.c2=9;
>
>  c1 | c3  | c2
> ----+-----+-----
>  a  | yyy | zzz


Your where clause is filtering out the values.  On the second record in X,
y.c2 is NULL so to get 2 rows you would need to write:

SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
 LEFT OUTER JOIN y ON (x.c2=y.c1)
WHERE y.c2=9 OR y.c2 IS NULL;




-- 
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================

In response to

pgsql-sql by date

Next:From: Frank BaxDate: 2008-05-04 16:54:29
Subject: Re: LEFT OUTER JOIN question
Previous:From: Craig RingerDate: 2008-05-04 16:40:24
Subject: Re: LEFT OUTER JOIN question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group