On 2019/02/18 11:24 AM, Clemens Ladisch wrote:
Rocky Ji wrote:
But everyone advices against nested select statements.
Who?
I've heard rumors that older version of the Oracle query optimizer did
worse with subqueries than with joins, but such advice is not necessarily
correct for SQLite.
+1
Whomever said to avoid nested or sub queries are lying to you - it's
like saying "Use only left turns when driving, avoid right turns" - it's
just silly, they both help to get you there.
That said, when you can achieve a result using only an outer query or a
join that may utilize an index, then sure, you should prefer that over a
sub query for efficiency purposes, but that in no way means to "avoid"
them completely. They are part and parcel of SQL and very much optimized
for (in modern DB engines) and often work faster and better.
Your advisors may have meant a specific older engine that had known
issues with sub-selects or such.
I see you telling Keith that the schema wording doesn't matter and that
the question is hypothetical, but have you actually run the schema you
made? It doesn't work because, as Keith pointed out, those are invalid
foreign keys. (It only runs if you set PRAGMA foreign_keys = 0; but that
negates the purpose, it should be on).
Remake the schema, use ABCD if that suits you better, but at least make
a schema that works, and restate the question. That way we can run the
schema on our side, compose the queries that would answer your question
without us having to spend half an hour first rewriting the schema into
a working one (which then may well destroy the premise of your question).
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
