SQLite CROSS JOIN with a Practical Example
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 LEFT JOIN
, INNER JOIN
, or CROSS JOIN
without the ON
or 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.
SELECT *
FROM A JOIN B;
SELECT *
FROM A
INNER JOIN B;
SELECT *
FROM A
CROSS JOIN B;
SELECT *
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 NxM
rows.
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:
SELECT name,
date
FROM members
CROSS JOIN dates;
SQLite CROSS JOIN
clause example
The following statements create the ranks
and 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)
VALUES('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('J'),('Q'),('K'),('A');
INSERT INTO suits(suit)
VALUES('Clubs'),('Diamonds'),('Hearts'),('Spades');
The following statement uses the CROSS JOIN
clause to return a complete deck of cards data:
SELECT rank,
suit
FROM ranks
CROSS JOIN
suits
ORDER BY suit;
rank | suit |
---|---|
2 | Clubs |
3 | Clubs |
4 | Clubs |
5 | Clubs |
6 | Clubs |
7 | Clubs |
8 | Clubs |
9 | Clubs |
10 | Clubs |
J | Clubs |
Q | Clubs |
K | Clubs |
A | Clubs |
2 | Diamonds |
3 | Diamonds |
4 | Diamonds |
5 | Diamonds |
6 | Diamonds |
7 | Diamonds |
8 | Diamonds |
9 | Diamonds |
10 | Diamonds |
J | Diamonds |
Q | Diamonds |
K | Diamonds |
A | Diamonds |
2 | Hearts |
3 | Hearts |
4 | Hearts |
5 | Hearts |
6 | Hearts |
7 | Hearts |
8 | Hearts |
9 | Hearts |
10 | Hearts |
J | Hearts |
Q | Hearts |
K | Hearts |
A | Hearts |
2 | Spades |
3 | Spades |
4 | Spades |
5 | Spades |
6 | Spades |
7 | Spades |
8 | Spades |
9 | Spades |
10 | Spades |
J | Spades |
Q | Spades |
K | Spades |
A | Spades |
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.