LEFT OUTER JOIN question

Hi!

I expect the SELECT to return two rows. Would some kind=20
soul explain for me why it gives only one row?

TIA

CN
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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=3Dy.c1)
WHERE y.c2=3D9;

 c1 | c3  | c2=20=20
----+-----+-----
 a  | yyy | zzz

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Comment: The following version of SELECT does return two=20
rows as expected, however:

SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
  LEFT OUTER JOIN y ON (x.c2=3Dy.c1);

 c1 | c3  |  c2=20=20
----+-----+------
 a  | yyy | zzz
 b  |     | zzzz

--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql