On Tue, 5 Mar 2019 08:13:32 +0000
Hick Gunter <h...@scigames.at> wrote:
> 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.
I'm not sure what you mean be "needs", above. If you're describing the
way the SQLite QP works, OK. If you're asserting that the QP or any QP
must work that way, no, that's common fallacy. The person writing the
query may think of a correlated subquery that way; it's *logically*
true. But the planner is free to execute the query however it
chooses. In fact, SQLite explains in great detail when the optimizer
will "flatten" a subquery into a join.
> select * from t1
> where c=1 and d in (select d from t2 where c=1);
> select * from t1
> where c=1 and d in (select d from t2 where t2.c=t1.c);
Consider:
select distinct t1.*
from t1 join t2
on t1.c = t2.c and t1.d = t2.d
where t1.c = 1
Every existential quantification can be recast as a join.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
