Re: EXISTS vs IN vs OUTER JOINS - Mailing list pgsql-performance

"Josh Berkus" <josh@agliodbs.com> writes:
> If I run the query:

> SELECT t1.*
> FROM table1 t1
>   LEFT JOIN table2 t2 ON t1.xid = t2.xid
> WHERE t2.label IS NULL

> I will get rows in t1 for which there is no row in t2.  This does not
> seem SQL-spec to me; shouldn't I get only rows from t1 where a row
> exists in t2 and t2.label IS NULL?

No; that would be the behavior of an inner join, but you did a left
join.  The above will give you t1 rows for which there is no match in t2
(plus rows for which there is a match containing null in t2.label).

            regards, tom lane