Learning Objective
The objective of this SQL Server tutorial is to teach you how to use CROSS JOIN to combine rows from two or more unrelated tables.
What is CROSS JOIN in SQL Server?
CROSS JOIN is used to combine the rows of two or more unrelated tables such that operation produces a resultset which is the Cartesian Product of the participating tables. Since the tables are unrelated, we do not have a to specify any column on which to JOIN the tables. CROSS JOINS are used to generate an exhaustive combination of datasets using data from the participating tables.
What is a CARTESIAN PRODUCT?
A CARTESIAN PRODUCT is a set operation which combines every element of a set with every other element of the other set. Suppose we have a SET A with x elements and another set B with y elements the result of the CARTESIAN PRODUCT will have x*y elements. To be more explicit, if set A has 2 elements a and b and set B has 3 elements 1, 2 and 3 then the CARTESIAN PRODUCT of A and B will be a1, a2, a3, b1, b2, b3.
It can be represented as a Venn Diagram like below.
Operation
A CROSS JOIN is implemented using the CROSS JOIN SQL clause in a SELECT statement and can be used to combine the datasets of two or more tables.
SQL Server CROSS JOIN Syntax
The basic syntax of CROSS JOIN is given below.
SELECT column_list FROM table1 CROSS JOIN table2
In this syntax,
- column_list – the list of columns to be used from the participating tables by the SELECT statement.
- table1 – the first or left table.
- table2 – the second or right table.
- CROSS JOIN – SQL keyword combination to implement a CROSS JOIN (where the resultset is a CARTESIAN PRODUCT of the participating tables).
SQL Server CROSS JOIN Examples
Let us see how we can use CROSS JOIN practically with an example. Suppose we have 3 tables in the database – a breakfast table containing breakfast item descriptions, a beverage table containing the list of beverages available and an additional table for additional or add-on items to complete a breakfast menu. The tables are represented below.
| item_no | item_name |
| 1 | French Toast |
| 2 | Bread Butter |
| 3 | Pasta |
| item_no | item_name |
| 1 | French Toast |
| 2 | Bread Butter |
| 3 | Pasta |
| item_no | item_name |
| 1 | Scrambled Eggs |
| 2 | Sausages |
1) SQL Server CROSS JOIN – two table example
We will do a CROSS JOIN on the first two tables (breakfast and beverage) with the following SQL query. We have aliased the breakfast table as b1 and beverage table as b2 and sorted the output by the first column (i.e. b1.item_name) of the resultset using the ORDER BY clause.
SELECT b1.item_name, b2.item_name FROM breakfast b1 CROSS JOIN beverage b2 ORDER BY 1;
It will produce the following output. We can see that Bread Butter has been combined with Tea, Coffee and Juice -Choice of Fruit (i.e. with every row of the other table). Likewise, for French Toast and Pasta and we have a total of 3*3=9 which is the cartesian product of the number of rows or records in the two tables.
| item_name | item_name |
| Bread Butter | Tea |
| Bread Butter | Coffee |
| Bread Butter | Juice – Choice of Fruit |
| French Toast | Tea |
| French Toast | Coffee |
| French Toast | Juice – Choice of Fruit |
| Pasta | Tea |
| Pasta | Coffee |
| Pasta | Juice – Choice of Fruit |
2) SQL Server CASE – three table example
Now let us try and CROSS JOIN all the 3 tables with the following query. We have aliased the breakfast table as b1, the beverage table as b2 and the additional table as a and sorted the output by the first column (i.e. b1.item_name) and second column (i.e. b.item_name) of the resultset using the ORDER BY clause.
SELECT b1.item_name, b2.item_name, a.item_name FROM breakfast b1 CROSS JOIN beverage b2 CROSS JOIN additional a ORDER BY 1, 2;
It will produce the following output. We can see that Bread Butter has been combined with Coffee and the combination has been combined with Scrambled Eggs and Sausages to produce 2 rows. Likewise, for the combination of Bread Butter and Juice and Bread Butter and Tea producing 2+2+2=6 rows and 6+6+6=18 rows taking into account all the 3 items (Bread Butter, French Toast and Pasta) of the first (breakfast) table. The same is equivalent to the cartesian product of the number of rows in the 3 tables i.e. 3*3*2=18.
| item_name | item_name | item_name |
| Bread Butter | Coffee | Scrambled Eggs |
| Bread Butter | Coffee | Sausages |
| Bread Butter | Juice – Choice of Fruit | Sausages |
| Bread Butter | Juice – Choice of Fruit | Scrambled Eggs |
| Bread Butter | Tea | Scrambled Eggs |
| Bread Butter | Tea | Sausages |
| French Toast | Coffee | Sausages |
| French Toast | Coffee | Scrambled Eggs |
| French Toast | Juice – Choice of Fruit | Scrambled Eggs |
| French Toast | Juice – Choice of Fruit | Sausages |
| French Toast | Tea | Sausages |
| French Toast | Tea | Scrambled Eggs |
| Pasta | Coffee | Scrambled Eggs |
| Pasta | Coffee | Sausages |
| Pasta | Juice – Choice of Fruit | Sausages |
| Pasta | Juice – Choice of Fruit | Scrambled Eggs |
| Pasta | Tea | Scrambled Eggs |
| Pasta | Tea | Sausages |
Thus, we can see how CROSS JOIN generates an exhaustive list of options combining the datasets of all the participating tables. This can be useful in stock taking at month-end, year-end or to generate a list of business options by a business application for planning and decision making.