SQLite Joins

14 Aug 2014

What is the difference between CROSS JOIN, INNER JOIN, JOIN and a comma (,)?

First of all, one thing to understand is all joins in SQLite is based on the cartesian product of left and right-hand datasets. In other words, if the left-hand dataset consists of Nleft rows of Mleft columns, and the right-hand dataset of Nright rows of Mright columns, then the cartesian product is a dataset of Nleft×Nright rows, each containing Mleft×Mright columns.

If the join-operator is “CROSS JOIN”, “INNER JOIN”, “JOIN” or a comma (“,”) and there is no ON or USING clause, then the result of the join is simply the cartesian product of the left and right-hand datasets.

When more than two tables are joined together as part of a FROM clause, the join operations are processed in order from left to right. In other words, the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).

Side note: Special handling of CROSS JOIN. There is no difference between the “INNER JOIN”, “JOIN” and “,” join operators. They are completely interchangeable in SQLite. The “CROSS JOIN” join operator produces the same result as the “INNER JOIN”, “JOIN” and “,” operators, but is handled differently by the query optimizer in that it prevents the query optimizer from reordering the tables in the join. An application programmer can use the CROSS JOIN operator to directly influence the algorithm that is chosen to implement the SELECT statement. Avoid using CROSS JOIN except in specific situations where manual control of the query optimizer is desired. Avoid using CROSS JOIN early in the development of an application as doing so is a premature optimization. The special handling of CROSS JOIN is an SQLite-specific feature and is not a part of standard SQL.

In conclusion, they are the same; but in some cases, some operators are more performant while the others are not due to special handling of CROSS JOIN by the query optimizer!