Обсуждение: outer join versus not exists

In pg, is there any performance gain in using outer join with null as
versus using not exists, eg:

select t1.* from t1 right join t2 using( id ) where t2.id is null;

versus

select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

On Tue, Mar 18, 2003 at 08:54:27 -0800, chester c young <chestercyoung@yahoo.com> wrote:
> In pg, is there any performance gain in using outer join with null as
> versus using not exists, eg:
> 
> select t1.* from t1 right join t2 using( id ) where t2.id is null;
> 
> versus
> 
> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);

You can use explain analyze to compare the queries.

Another possible approach is:

select t1.* from t1, (select id from t1 except select id from t2) t3 where t1.id = t3.id;

chester c young <chestercyoung@yahoo.com> writes:
> In pg, is there any performance gain in using outer join with null as
> versus using not exists, eg:

> select t1.* from t1 right join t2 using( id ) where t2.id is null;

You meant a left join here, no?

> versus

> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);

It depends.  Either one could be substantially better than the other,
depending on the data statistics.

If many of the t1 rows have multiple matches in t2, I'd think that the
join approach would lose, because it would generate lots of joined rows
that only get thrown away.

If t2.id is unique (and indexed), then the exists approach would
probably give you a plan approximately equivalent to a nestloop with
inner indexscan on t2.  This would be one of the alternatives considered
for the join, so the join should be at least as fast as the exists,
possibly faster if the planner can find a faster plan involving merge or
hash joining.
        regards, tom lane