From: | "seiliki" <seiliki(at)so-net(dot)net(dot)tw> |
---|---|
To: | "" <pgsql-sql(at)postgresql(dot)org> |
Subject: | LEFT OUTER JOIN question |
Date: | 2008-05-04 16:28:04 |
Message-ID: | 1209918484.14262.seiliki@so-net.net.tw |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
=========================
Comment: The following version of SELECT does return two
rows as expected, however:
SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
LEFT OUTER JOIN y ON (x.c2=y.c1);
c1 | c3 | c2
----+-----+------
a | yyy | zzz
b | | zzzz
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-05-04 16:40:24 | Re: LEFT OUTER JOIN question |
Previous Message | Volkan YAZICI | 2008-05-03 14:36:49 | Re: update with multiple fields as aggregates |