[SQL] LEFT OUTER JOIN question

[seiliki]

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','');

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  | | 

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

Re: [SQL] LEFT OUTER JOIN question

[Craig Ringer]

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


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

Re: [SQL] LEFT OUTER JOIN question

[Aaron Bono]

On Sun, May 4, 2008 at 11:28 AM, seiliki <[EMAIL PROTECTED]> 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','');
>
> 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
==

Re: [SQL] LEFT OUTER JOIN question

[Frank Bax]

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.

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