On 5 Mar 2019, at 2:06am, kk <kmccord0...@gmail.com> wrote:
>>> 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);
> DRH, many thanks for your reply, I was expecting same output because I
> believe stmts to be equivalent, so was not sure why query plan was different.
> I see the explain plans are very similar.
> But I believe original stmts mentioned are still equivalent?
How do you expect a SQL engine to approach the above statements ? Should it
process the inner SELECT first, or the outer SELECT first ?
If it processes the inner SELECT first, where does it get the value it needs
for t1.c ?
If it processes the outer SELECT first, what strategy does it use for selecting
on t1.d when it doesn't yet know whether there's going to be no, a single, or
multiple values ?
> Do you agree? And in SQLite what is best way to write such stmt (or in other
> terms, what is difference)?
Using a JOIN.
SELECT t1.* FROM t1
INNER JOIN t2 ON t2.c=1 AND t2.d = t1.d
WHERE t1.c=1;
SELECT t1.* FROM t1
INNER JOIN t2 ON t2.c=t1.c AND t2.d = t1.d
WHERE t1.c=1;
The INNER JOIN (as opposed to OUTER JOIN) means that a row must exist in t2 for
the equivalent row in t1 to be returned. INNER is the default kind of JOIN.
Of the two statements, it seems that the fist one requires less processing.
Internally, SQLite does comparison and conversion when faced with different
ways of phrasing your query. But that's not your problem. Phrase what you
want in as specific terms as possible, and let SQLite pick its preferred way of
solving the problem.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
