How to use SQL Cross Join with Examples?
Table of Contents
Overview of SQL Cross Join
In this type of join the result is a Cartesian product of the 2 tables (or more, if specified). This means that each row from first table is associated with each row from second table.
If there are X and Y number of rows in first and second table respectively then the resultant table will contain X*Y number of rows.
Keyword CROSS JOIN is used to specify this type of join. However, if you don’t specify the keyword it will still produce the Cartesian product but it is a good practice to mention the keyword as it will make sure you haven’t done this accidentally.
In case of multiple tables in the join query, the evaluation order is left to right.
Syntax
SELECT * FROM table1, table2;
Or
SELECT * FROM table1 CROSS JOIN table2;Example
For convenience, we will use the same 3 tables as defined in the previous post SQL JOIN Explained with examples.
SQL Cross Join with 2 tables
Lets join the tables customer and orders. Table customer has 4 rows and table orders has 7 rows.
select * from customer, orders;
Or,
select * from customer cross join orders;Result
+-------------+---------+------------------+----------+-------------+---------------------+
| customer_id | name | email | order_id | customer_id | order_date |
+-------------+---------+------------------+----------+-------------+---------------------+
| 103 | Grey | grey@test.com | 10 | 100 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 10 | 100 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 10 | 100 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 10 | 100 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 11 | 100 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 11 | 100 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 11 | 100 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 11 | 100 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 12 | 101 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 12 | 101 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 12 | 101 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 12 | 101 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 13 | 103 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 13 | 103 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 13 | 103 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 13 | 103 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 14 | 106 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 14 | 106 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 14 | 106 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 14 | 106 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 15 | 107 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 15 | 107 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 15 | 107 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 15 | 107 | 2020-03-01 21:37:55 |
| 103 | Grey | grey@test.com | 16 | 108 | 2020-03-01 21:37:55 |
| 102 | John | john@test.com | 16 | 108 | 2020-03-01 21:37:55 |
| 101 | Robert | robert@test.com | 16 | 108 | 2020-03-01 21:37:55 |
| 100 | Michael | michael@test.com | 16 | 108 | 2020-03-01 21:37:55 |
+-------------+---------+------------------+----------+-------------+---------------------+
28 rows in set (0.00 sec)Both the queries produced the same result having 28 (4*7) rows.
SQL Cross Join with 3 tables
Lets join the 3rd table as well i.e. invoice. Here, this table has 8 rows.
Query
select count(*) from customer, orders, invoice;
Or
select count(*) from customer cross join orders cross join invoice;
Result
+----------+
| count(*) |
+----------+
| 224 |
+----------+
1 row in set (0.01 sec)Here we are getting 224 rows i.e. 4*7*8 as expected.
Although we don’t often come across scenarios where we need Cartesian product of tables but if you do at all, put the keyword CROSS JOIN in the query to make sure you haven’t forget mentioning the join type.