Re: PostgreSQL NOT IN performance

2008/11/19 Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>

>
> On Wed, 19 Nov 2008, [ISO-8859-5] Віталій Тимчишин wrote:
>
> > Query 1:
> > select * from t1 where id not in (select id from t2);
> >
> > Query 2 (gives same result as Q1):
> > select * from t1 except all (select id from t2);
>
> It gives the same result as long as no nulls are in either table. If
> either table can have a null, the conversion changes the results.
>
> In addition, a conversion like the above only happens to work because t1
> only has an id column. If t1 had two columns you'd get an error because
> the two sides of except all must have the same number of columns.
>

Actually It can be done even for multi-column mode if the selection is done
on unique key. It would look like:

select * from t1 inner join (
select id from t1 except select id from t2) talias on t1.id = talias.id

And it would produce better results then "not in" for large counts in t1 and
t2.