Thread: LEFT OUTER JOIN question
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 | zzzb | | zzzz
seiliki wrote: > Hi! > > I expect the SELECT to return two rows. Would some kind > soul explain for me why it gives only one row? Without having read the post in detail I'll make a guess: Because NULL = NULL results in NULL, not true, and the outer (or any other) join condition only accepts rows where the join condition is true. This is a FAQ. It probably needs to go in the PostgreSQL FAQ. The usual response is: Rethink your use of NULL values. Null really means "unknown" and if you're comparing for equality you probably don't really want NULLs to be present. If you absolutely must perform comparisons where NULL should be considered equal to NULL use `IS DISTINCT FROM` ... but as everybody here says, use of that often suggests design problems in your queries and schema. -- Craig Ringer
On Sun, May 4, 2008 at 11:28 AM, seiliki <seiliki@so-net.net.tw> wrote:
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
==================================================================
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
==================================================================
Craig Ringer wrote: > seiliki wrote: >> I expect the SELECT to return two rows. Would some kind >> soul explain for me why it gives only one row? > > Without having read the post in detail I'll make a guess: Because NULL = > NULL results in NULL, not true, and the outer (or any other) join > condition only accepts rows where the join condition is true. > > This is a FAQ. It probably needs to go in the PostgreSQL FAQ. > > The usual response is: Rethink your use of NULL values. Null really > means "unknown" and if you're comparing for equality you probably don't > really want NULLs to be present. If you absolutely must perform > comparisons where NULL should be considered equal to NULL use `IS > DISTINCT FROM` ... but as everybody here says, use of that often > suggests design problems in your queries and schema. Perhaps you should have read the post in detail. There is no NULL=NULL comparison here. If you add column y.c2 to the SQL that produces two rows; you will see that y.c2 is NULL; which is not meet where condition of y.c2=9 in first SQL so therefore row is not included in results.