Dinu wrote: > I triple-checked it and it is indeed generating different execution plans
Probably different indexes? Are these actual tables? >> select * from t where exists (select * from lookup where x = t.x); >> select * from t where x in (select x from lookup); >> select t.* from t join lookup using (x); > > However, I am a bit confused by the examples you provided: > The 3rd query is the equivalent of the first 2 only if the lookup table has > an unique index on x (which it does). However, this would make a very > restricted case of why you would use EXISTS(). It's just another example of how a query is executed the same; I was not implying that it would be preferrable over the other ones. (The third case is interesting if there is no index on the lookup column, because with a join, SQLite estimates that it's worthwhile to create a temporary index on the lookup column. IN would create a temporary index for the result of the subquery, while EXISTS would still execute the subquery (now a table scan) for every outer row.) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
