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 

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?

-----Original Message-----
From: sqlite-users [] On 
Behalf Of R Smith
Sent: Thursday, September 07, 2017 8:06 AM
Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 3:43 AM, Keith Medcalf wrote:
> Try the same test using 147 columns in each table.

Exactly the plan for this weekend :)

> 1 column is rather trivial.  Even a kindergarten kid could do it in no time 
> using crayons and the wall.

So? That is non-sequitur, I am sure given enough crayons, wall-space and 
time, a kindergarten kid can do it with 147 columns too. That says 
exactly nothing about the possible efficiencies of different methods. If 
however the 1-columness of the test gets somehow advantaged by being the 
PK (as Nico pointed out) or real world data such as TEXT entries sort 
slower than INTs, then it might affect it, so the 147 column tests will 

> In other words except in very trivial cases (like having only one column that 
> is not nullable) it will be very difficult to write a "correct" JOIN or 
> correlated subquery that emulates an INTERSECT.

Well I agree, but it is those trivial cases that are of interest here, 
and if there is a general JOIN optimization to be had. The INTERSECT 
test merely served as the catalyst to put us on the trail of the 
possible JOIN optimization, if there is even an optimization to be had 
(it might yet be a wild goose chase, which you seem to have your money 
on, so watch this space, I'll graciously accept your "told ya!" later 
after testing).


sqlite-users mailing list
sqlite-users mailing list

Reply via email to