Constantin Emil MARINA wrote: > I am wondering if in SQLITE the EXISTS clause is expanded and optimized in > any way.
No. > This is generated by the observation that 2 algebrically equivalent queries, > SELECT WHERE EXISTS () > and > SELECT WHERE id IN (SELECT ...) > produce different execution plans and different performance, with WHERE id IN > (SELECT ) looking properly optimized. All three ways of writing this query are executed in the same way. In particular, the IN and JOIN queries are rewritten to act like the EXISTS query: create table t(x); create table lookup(x primary key); select * from t where exists (select * from lookup where x = t.x); --EQP-- 0,0,0,SCAN TABLE t --EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1 --EQP-- 1,0,0,SEARCH TABLE lookup USING COVERING INDEX sqlite_autoindex_lookup_1 (x=?) select * from t where x in (select x from lookup); --EQP-- 0,0,0,SCAN TABLE t --EQP-- 0,0,0,USING INDEX sqlite_autoindex_lookup_1 FOR IN-OPERATOR select t.* from t join lookup using (x); --EQP-- 0,0,0,SCAN TABLE t --EQP-- 0,1,1,SEARCH TABLE lookup USING COVERING INDEX sqlite_autoindex_lookup_1 (x=?) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
