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

Reply via email to