Re: PostgreSQL NOT IN performance

["Віталій Тимчишин" <tivv00@xxxxxxxxx>]

2008/11/19 Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx>

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.