`INTERSECT` vs `INNER JOIN` in PDO SQLite

Question

I wonder which way is faster

SELECT Id FROM T1
INTERSECT
SELECT Id FROM T2

or

SELECT T1.Id
    FROM T1
    INNER JOIN T2 ON T1.Id=T2.Id

Answer 1

At the moment, SQLite implements INTERSECT by copying the results of the two queries into two temporary sorted tables, and then looking up each Id value of the first table in the second table.

An INNER JOIN is implemented as a nested loop join, i.e., each Id value of one table is looked up in the other table. (SQLite chooses the other table as the one with an index on Id; if neither table has such an index, it creates a temporary index.)

So the pratical difference is that INTERSECT always creates temporary tables, while JOIN can work directly on the actual tables. (If T1 and T2 were complicated subqueries, JOIN would also need temporary tables, and there would be no difference.)