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

Re: EXISTS vs IN vs OUTER JOINS

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Josh Berkus" <josh(at)agliodbs(dot)com>
Cc: jasiek(at)klaster(dot)net,PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: EXISTS vs IN vs OUTER JOINS
Date: 2002-12-20 00:02:13
Message-ID: 10090.1040342533@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Josh Berkus" <josh(at)agliodbs(dot)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

In response to

pgsql-performance by date

Next:From: Manfred KoizarDate: 2002-12-20 08:53:56
Subject: Re: EXISTS vs IN vs OUTER JOINS
Previous:From: Josh BerkusDate: 2002-12-19 23:19:21
Subject: Re: EXISTS vs IN vs OUTER JOINS

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