Expanding things for when you get bored, in addition to <INNER> JOIN vs INTERSECT vs IN I'd also be interested in <OUTER> JOIN vs EXCEPT vs NOT IN, as I tend to do more exclusion rather than intersection.
The straight up "IN tablename" may be SQLite only, but it also supports IN (subquery) so "select v from t1 where v in (select v from t2)" also works, although the "IN tablename" is so much cleaner to read in my opinion. create table statesToSkip ( state text primary key collate nocase ) without rowid; select foo from bar where state not in statesToSkip. vs select foo from bar where state not in (select state from statesToSkip) vs select bar.foo from bar left outer join statesToSkip on (bar.state = statesToSkip.state) where statesToSkip.state is null; -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, September 07, 2017 3:51 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency On 2017/09/07 6:31 PM, David Raymond wrote: > Although it may not translate as well to the more complex examples, would you > also consider adding the IN operator to your tests? I found for example that > "select v from t1 where v in t2;" did even better than the join or the > intersect. Will do. The only thing I have somewhat against that specific query form is that it doesn't work in other engines (seems to not be standard). But for optimization in SQLite specifically that's perfect. > Other "am I right in thinking this" question: INTERSECT is only going to be > viable when comparing full records, correct? If you're looking to filter > table A by whether its primary key is also a primary key for table B, but > ignoring the other fields in both, then INTERSECT becomes not an option, or > at least starts making the query more complex/ugly... correct? INTERSECT will happily match however many columns you desire (and specify), there is no need to match full records or single keys specifically. _______________________________________________ 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
