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

Reply via email to