Both statements generate the same result set, but they are neither equivalent nor equally fast.
The first statement uses a *constant* subquery as the RHS of an IN expression. The QP is free to materialize this query (i.e. run it once and keep the results in an "ephemeral" table with an index for fast lookup). The second statement uses a *correlated* subquery as the RHS of an IN expression. The QP needs to actually run this query for every record of t1 that matches the condition t1.c == 1. Imagine what happens if the constraint t1.c == 1 is changed to t1.c == 2. The first statement will still be checking against the same constant subquery result set. The second statement will be checking against a new and possibly quite different correlated subquery result set. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Kyle Gesendet: Dienstag, 05. März 2019 02:05 An: [email protected] Betreff: [EXTERNAL] [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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
