SQLite CROSS JOIN with a Practical Example

Created with Sketch.

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;

 

ranksuit
2Clubs
3Clubs
4Clubs
5Clubs
6Clubs
7Clubs
8Clubs
9Clubs
10Clubs
JClubs
QClubs
KClubs
AClubs
2Diamonds
3Diamonds
4Diamonds
5Diamonds
6Diamonds
7Diamonds
8Diamonds
9Diamonds
10Diamonds
JDiamonds
QDiamonds
KDiamonds
ADiamonds
2Hearts
3Hearts
4Hearts
5Hearts
6Hearts
7Hearts
8Hearts
9Hearts
10Hearts
JHearts
QHearts
KHearts
AHearts
2Spades
3Spades
4Spades
5Spades
6Spades
7Spades
8Spades
9Spades
10Spades
JSpades
QSpades
KSpades
ASpades

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.

Leave a Reply

Your email address will not be published. Required fields are marked *