SQLite CROSS JOIN

The SQLite CROSS JOIN returns the Cartesian product of the tables used in the join. SQLite CROSS JOIN returns all rows from both tables, if the ON clause with matching condition is not used.

Syntax

The SQLite CROSS JOIN syntax is as follows:

SELECT column1, column2, ...
FROM left_table
CROSS JOIN right_table;

Example

Table of students

student_id	first_name	last_name	birthday	address_id
1	Paul	Smith	2002-10-14	100
2	Charlotte	Jones	2003-05-18	101
3	Theodore	Hernandez	2003-08-12	102
4	Susan	Taylor	2004-07-24	NULL

Table of student_address

address_id	city	country
100	San Antonio	US
101	San Jose	US
102	Philadelphia	US
103	Austin	US
104	Boston	US
105	Seattle	US

In the following example, a CROSS JOIN is made between the students table and the student_address table. The ON clause with matching condition is used. Practically, when you use the ON clause with CROSS JOIN, the result will be the same INNER JOIN call.

SELECT s.*
FROM students s 
CROSS JOIN student_address sa 
ON s.address_id = sa.address_id;

Output

student_id	first_name	last_name	birthday	address_id
1	Paul	Smith	2002-10-14	100
2	Charlotte	Jones	2003-05-18	101
3	Theodore	Hernandez	2003-08-12	102

In the second example, the query performs a CROSS JOIN between the students table and the student_address table without using the ON clause with a matching condition. Basically CROSS JOIN will return the Cartesian product.

SELECT s.*
FROM students s 
CROSS JOIN student_address sa

Output

student_id	first_name	last_name	birthday	address_id
1	Paul	Smith	2002-10-14	100
1	Paul	Smith	2002-10-14	100
1	Paul	Smith	2002-10-14	100
1	Paul	Smith	2002-10-14	100
1	Paul	Smith	2002-10-14	100
1	Paul	Smith	2002-10-14	100
2	Charlotte	Jones	2003-05-18	101
2	Charlotte	Jones	2003-05-18	101
2	Charlotte	Jones	2003-05-18	101
2	Charlotte	Jones	2003-05-18	101
2	Charlotte	Jones	2003-05-18	101
2	Charlotte	Jones	2003-05-18	101
3	Theodore	Hernandez	2003-08-12	102
3	Theodore	Hernandez	2003-08-12	102
3	Theodore	Hernandez	2003-08-12	102
3	Theodore	Hernandez	2003-08-12	102
3	Theodore	Hernandez	2003-08-12	102
3	Theodore	Hernandez	2003-08-12	102
4	Susan	Taylor	2004-07-24	NULL
4	Susan	Taylor	2004-07-24	NULL
4	Susan	Taylor	2004-07-24	NULL
4	Susan	Taylor	2004-07-24	NULL
4	Susan	Taylor	2004-07-24	NULL
4	Susan	Taylor	2004-07-24	NULL

↑