Summary: in this tutorial, you will learn how to use SQLite
CROSS JOIN to combine two or more result sets from multiple tables.
Introduction to SQLite
CROSS JOIN clause
If you use a
INNER JOIN, or
CROSS JOIN without the
USING clause, SQLite produces the Cartesian product of the involved tables. The number of rows in the Cartesian product is the product of the number of rows in each involved tables.
Suppose, we have two tables A and B. The following statements perform the cross join and produce a cartesian product of the rows from the A and B tables.
FROM A JOIN B;
INNER JOIN B;
CROSS JOIN B;
FROM A, B;
Suppose, the A table has N rows and B table has M rows, the
CROSS JOIN of these two tables will produce a result set that contains
Imagine that if you have the third table C with
K rows, the result of the
CROSS JOIN clause of these three tables will contain
NxMxK rows, which may be very huge. Therefore, you should be very careful when using the
CROSS JOIN clause.
You use the
INNER JOIN and
LEFT JOIN clauses more often than the
CROSS JOIN clause. However, you will find the
CROSS JOIN clause very useful in some cases.
For example, when you want to have a matrix that has two dimensions filled with data completely like members and dates data in a membership database. You want to check the attendants of members for all relevant dates. In this case, you may use the
CROSS JOIN clause as the following statement:
CROSS JOIN dates;
CROSS JOIN clause example
The following statements create the
suits tables that store the ranks and suits for a deck of cards and insert the complete data into these two tables.
CREATE TABLE ranks (
rank TEXT NOT NULL
CREATE TABLE suits (
suit TEXT NOT NULL
INSERT INTO ranks(rank)
INSERT INTO suits(suit)
The following statement uses the
CROSS JOIN clause to return a complete deck of cards data:
ORDER BY suit;
In this tutorial, you have learned how to use the SQLite CROSS JOIN clause to produce a Cartesian product of multiple tables involved in the join.