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 |