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