In the first query the subselect that creates the list is independent. In the second query the subselect that creates the list is correlated.
In the first query you have requested that the subquery be executed to create the list for use by the IN operator. After this has been done the main (outer) query is executed and a result generated when the where condition (which includes the IN operator) are satisfied. Since it is possible that the list may not need to be generated because the condition c==1 in the outer query may never be satisfied, the subquery is only executed ONCE the first time its results are required. In the second query you have requested that the outer query be executed AND FOR EACH ROW that passes the WHERE c=1 constraint, execute the subquery and then check if d in the outer query is in the set of the results obtained by running the correlated subquery. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Kyle >Sent: Monday, 4 March, 2019 18:05 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Equiv stmts, different explain plans > >On another DB I came across 2 stmts, that I think are equivalent, but >generated different explain plans. I request a second opinion - are >these 2 stmts equivalent? If so, why do they generate different >explain >plans even on sqlite? >TIA >-- >create table t1(c,d); >create table t2(c,d); >explain select * from t1 > where c=1 and d in (select d from t2 where c=1); >explain select * from t1 > where c=1 and d in (select d from t2 where t2.c=t1.c); >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
