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

Re: LEFT OUTER JOIN question

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: LEFT OUTER JOIN question
Date: 2008-05-04 16:54:29
Message-ID: 481DEA45.4010603@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

pgsql-sql by date

Next:From: Ryan WallaceDate: 2008-05-06 00:22:29
Subject: Working with dates before 4713 BC
Previous:From: Aaron BonoDate: 2008-05-04 16:46:17
Subject: Re: LEFT OUTER JOIN question

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