A "result set limiting" constraint associates a field with a constant (or a variable), whereas a "join constraint" defines a relation between two fields of the tables being joined. There are three (not including syntax variations) logically equivalent ways to express an equijoin between two tables limited to a certain value of the field used: 1) T1.a == x AND T1.a == T2.a 2) T2.a == x AND T1.a == T2.a 3) T1.a == x AND T2.a == x Form 1 favors T1 in the outer loop Form 2 favors T2 in the outer loop Form 3 is indifferent Combining Form 1 with a cross join with T2 in the outer loop makes SQLite check T2.a == x inside the full table scan loop instead of asking for a partial key scan of the same condition.