On 2019/03/05 4:06 AM, kk wrote:
On 05/03/2019 01:33, Richard Hipp wrote:
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);
DRH, many thanks for your reply, I was expecting same output because I
believe stmts to be equivalent//...
They are very much not equivalent. They happen to produce the same
output with this very specific crafted schema and queries, but that does
not say that they mean the same thing, in fact they mean very different
things in execution. I think Keith explained it well enough technically,
but in case it is not 100% clear yet, let me add to it this example:
Say we have a group of random people, and I asked you to separate out
all the people aged above 25, and then from that group separate out all
the women, and then from that group separate all who have
husbands/partners in the original group.
The next day, with the same group of beings, I might ask to first
separate out all partnered pairs from the group, then from that group
separate out all females and from that remainder, get everything that's
been on Earth more than 25 years.
You might rightfully protest that, in the end, we would have the exact
same people we've already picked out yesterday, and it would be true -
however, the intermediate groups along the execution plan look very
different, and the method you've used to achieve this second result
follows a very different set of instructions, and, if the origin group
allowed non-humans in, the second query may actually yield different
results.
They are not equivalent in function just because they happen to yield
the same end-results for the specific schema and content.
Hope that is a useful clarification!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
