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;

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.