2008/11/19 Stephan Szabo <[EMAIL PROTECTED]> > > 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.
