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
