Whoa! Big revelation. I didn't know foreign keys were disabled by default. And my code just ran in sqlite3 shell, and this made me think text and rowid and etc foreign keys "just worked".
Sorry for the ruckus. Here's the new thing: https://pastebin.com/raw/pSqjvJdZ Again, can we get rid of them sub-query? Be nice. On Mon, Feb 18, 2019, 3:22 PM R Smith <ryansmit...@gmail.com wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
