The SQL Server CROSS JOIN returns the Cartesian product of two or more tables. The Cartesian product means the total number of rows in Table 1 multiplied by the number of rows in Table 2. The CROSS JOIN is very useful when you want to display all the possible combinations in two columns to analyse the data.
SQL CROSS JOIN Scenario: In your regular day-to-day life, you see a menu in famous restaurants with a separate page for food items, drinks, salads, desserts, etc. Apart from those, there are combo pages that display the most popular or the best possible combinations of food, drinks, salads, and desserts. To get this combo, you need the CROSS JOIN because it displays all possible combinations of them.
The SQL Server CROSS JOIN does not require any common column to join two tables. Let us see the visual representation of the Cartesian Join for a better understanding.
NOTE: When working with a table having a large number of rows, the CROSS JOIN will produce a huge number of records, which will impact performance. To avoid this, use the WHERE clause to filter the data, and the TOP Clause to choose a limited percentage of data, or use other joins.
SQL CROSS JOIN Syntax
The common syntax of the CROSS JOIN is shown below. It selects all the columns present in Table1 and Table2.
SELECT * FROM Table1 CROSS JOIN Table2
To explicitly specify a few column names from two tables, use the code below.
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
CROSS JOIN Table2
The result of a CROSS JOIN is equal to the product of the total number of rows in two tables. For instance, if Table1 has m (4) rows and Table2 has n (5) rows, the result set contains m (4) × n (5) rows (20).
The older version of the syntax avoids the CROSS JOIN name, and SQL Server will implicitly consider it as such. We can write the syntax by specifying the two table names separated by a comma.
The code below works the same as the syntax mentioned above and returns the same result.
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1, Table2
TIP: Unlike other JOINS, the CROSS JOIN does not require any joining condition, because it simply returns the Cartesian product.
To demonstrate the SQL Server CROSS JOIN, we created two new tables, ‘foods’ and ‘drinks’, each having three items. The data present in both the SQL Server tables is shown below. Here, we used the Prices in USD for the sake of understanding.
Foods
| id | Food | FPrice |
|---|---|---|
| 1 | Chicken Tacos | 7.00 |
| 2 | Margherita Pizza | 11.00 |
| 3 | Chocolate Brownie | 4.50 |
Drinks
| id | Drink | DPrice |
|---|---|---|
| 1 | Iced Tea | 3.50 |
| 2 | Cold Brew Coffee | 4.00 |
| 3 | Sparking Lime Water | 3.50 |
SQL CROSS JOIN Example
Suppose you have a food table and a drinks table, and you want to see the possible combinations of food items with drinks.
To demonstrate the SQL CROSS JOIN, we use the SELECT * FROM statement with the two tables mentioned above. The following query will display all the existing food & drink table columns. By this, you can see the combination of each food item with three combinations of drinks.
SELECT * FROM foods
CROSS JOIN drinks
If you observe the result set, it displays 9 records. It means three rows from the foods multiplied by three rows in the drinks table.
Apart from the above approach, you can use the following query without the CROSS JOIN keyword to achieve the same result.
SELECT * FROM foods, drinks
NOTE: I suggest you select individual column names. Please avoid the SELECT * Statement to avoid unwanted columns like id.
SELECT a Few Columns Example
As we said before, please select the required columns from food and drinks after the SELECT Statement to avoid unwanted columns in SQL CROSS JOIN. For example, the following query displays the Cartesian product of the Food and Drink items along with their prices.
SELECT Food, FPrice, Drink, DPrice FROM foods
CROSS JOIN drinks
Using ORDER BY Clause
If you observe the result set of the above example, it returns the records in no particular order. However, combining the SQL CROSS JOIN with the ORDER BY Clause, you can control the order in which the records are displayed. For instance, the query below orders the food and drinks by the Food items in ascending order.
SELECT Food, FPrice, Drink, DPrice FROM foods
CROSS JOIN drinks
ORDER BY Food
Using CROSS JOIN to Generate a Report
The two tables that we provided have some amazing combinations, and what if we want to display the pricing for each combination? It allows the user to pick the favourite combos. The query below returns the combinations, and for this, we used CONCAT to combine food and drinks. Next, the Arithmetic + operator is used to add the costs of food and drink.
SELECT CONCAT(Food, ' - ', Drink) AS Food, FPrice + DPrice AS Price
FROM foods
CROSS JOIN drinks
ORDER BY Food
SQL CROSS JOIN Ambiguous Columns Error: Fix it!
The above transact query will work flawlessly if column names from both tables (Food and Drinks) are different, like above. But what happens if they had the same column names in both tables? Well, the above-specified approach will throw an error.
Let me show you a practical example. As we already know, both tables have id column and if we added the id from the food table as an additional column to the above query.
SELECT id, Food, FPrice, Drink, DPrice FROM foods
CROSS JOIN drinks
As you see the message, the SQL CROSS JOIN is throwing an error: Ambiguous column name id. The id column is present on both tables. And the Server doesn’t know which column you are asking it to retrieve.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'id'.To resolve these issues, you must use the table name before the column name. For example, the following CROSS JOIN query uses the ALIAS table name before the column names. By this approach, we can inform the Server that we are looking for the id column belonging to the food table.
We can write the above Cross Join query to solve the ambiguous column error using ALIAS names as:
SELECT f.id, f.Food, f.FPrice, d.Drink, d.DPrice
FROM foods AS f
CROSS JOIN drinks AS d
SQL CROSS JOIN With WHERE Clause (Filtering)
As we said earlier, the CROSS JOIN don’t need any ON condition to join the two tables. However, we can use the WHERE Clause to restrict the number of rows returned (something like filtering).
The following query uses the WHERE clause with the AND operator to combine two conditions. It returns the food and drinks combination where the food item price is greater than or equal to 7 and the drink price is greater than 4.
SELECT Food, FPrice, Drink, DPrice
FROM foods
CROSS JOIN drinks
WHERE FPrice >= 7 AND DPrice > 4
Food FPrice Drink DPrice
Chicken Tacos 7.00 Iced Tea 4.50
Margherita Pizza 11.00 Iced Tea 4.50
Chicken Tacos 7.00 Cold Brew Coffee 5.00
Margherita Pizza 11.00 Cold Brew Coffee 5.00NOTE: The WHERE clause applies a filter after the Cartesian products of two tables are generated. So, the CROSS JOIN first generates 9 rows (possible combinations) and then the WHERE clause filters them using the condition to return those 4 rows.
SQL CROSS JOIN with Aggregate Functions
While performing the CROSS JOIN to find the Cartesian product of the two tables, applying any aggregate function will provide the result by group. For instance, the query below groups the Cartesian product by Food and counts the total number of drink combinations, as well as the sum of all drink prices, plus the cost of each food item (three times).
If you observe the result set of the SELECT * example, each food item has three combinations with those three drinks. So, when we calculate the SUM, the food price will be three times the cost of the three drinks. If you take Chicken Tacos:
- Chicken Tacos (7) + Iced Tea (4.5) = 11.5
- Chicken Tacos (7) + Cold Brew Coffee (5) = 12
- Chicken Tacos (7) + Sparking Lime Water (3.5) = 10.5
The total sum = 34. Use the same technique for the remaining two food items.
SELECT Food, COUNT(Drink) AS TotalDrinks, SUM(FPrice + DPrice) AS Price
FROM foods
CROSS JOIN drinks
GROUP BY Food
Food TotalDrinks Price
Chicken Tacos 3 34.00
Chocolate Brownie 3 26.50
Margherita Pizza 3 46.00If you can access the AdventureWorks database, use the query below to find the total sales for each product category.
SELECT c.ProductCategoryID AS ID, c.Name AS Category, SUM(od.LineTotal) AS TotalSales
FROM Production.ProductCategory c
JOIN Production.ProductSubcategory sc ON sc.ProductCategoryID = c.ProductCategoryID
JOIN Production.Product p ON p.ProductSubcategoryID = sc.ProductSubcategoryID
JOIN Sales.SalesOrderDetail od ON od.ProductID = p.ProductID
GROUP BY c.ProductCategoryID, c.Name
ORDER BY c.ProductCategoryID;
Using SQL CROSS JOIN with subqueries
To enable more powerful dynamic filtering, use the CROSS JOIN with a subquerySQL Subquery. Here, the subquery filters the drinks table and displays the records whose drink name starts with either I or C (2 rows). Next, the CROSS JOIN performs a Cartesian product of these two against the three food items to get 6 rows.
SELECT Food, FPrice, d.Drink, d.DPrice
FROM foods
CROSS JOIN
(SELECT Drink, DPrice FROM drinks WHERE Drink LIKE N'[IC]%') AS d
Use SQL CROSS JOIN to combine three or Multiple tables?
In all our previous examples, we used the CROSS JOIN to combine two tables, but you can also use three, four, or multiple tables. For instance, if our food database has another table called ‘extras’. It has two records that are a great combination with the above-mentioned food and drinks. So, to display the mega combo menu, we must combine these three tables.
Extras
| id | Items | EPrice |
|---|---|---|
| 1 | Mixed Green Salad | 5.00 |
| 2 | Fresh Fruit Cup | 4.00 |
The query below displays all the combinations of food items, drinks, and add-ons. To display it as a combo menu, the COCAT will combine the three names, and the + operator calculates the sum of all three items for each combo meal.
SELECT foods.id, Food, FPrice, Drink, DPrice, Items, EPrice,
CONCAT(Food, ' - ', Drink, ' - ', Items) AS 'Food Combo',
(FPrice + DPrice + EPrice) AS 'Combo Cost'
FROM foods
CROSS JOIN drinks
CROSS JOIN extras
ORDER BY [Food Combo]
FAQs
The following is a list of some of the frequently asked questions about SQL CROSS JOIN.
When to use a CROSS JOIN?
As we said earlier, the CROSS JOIN creates all possible combinations of two tables. So, the following are some of the use case scenarios that you use the SQL CROSS JOIN.
- To see all the combinations for a better understanding of the products.
- Generate a sample dataset for testing or data analysis.
- To create pivot tables.
- Generate data to train data models.
- If there is no common column for joining, use this to see the data because a CROSS JOIN does not require any condition to perform the joining operation.
- Using any aggregated function, you can calculate the total orders, sales, average, etc, of each possible product combination.
What is the Difference between SQL UNION and CROSS JOIN?
The UNION operator combines the records from two tables and returns in a single column. In our case, three food items and the three drinks will return as a single column (a total of 6 rows).
SELECT Food FROM foods
UNION
SELECT Drink FROM drinks
On the other hand, the CROSS JOIN returns the Cartesian product (every combination of food against each drink). It displays the records side by side to view the combinations.
What is the Difference between SQL CROSS JOIN and INNER JOIN?
The fundamental difference is that the CROSS JOIN don’t need any condition to perform JOINS between tables, whereas the INNER JOIN needs it. If you use the CROSS JOIN with a WHERE clause, it acts the same as the INNER JOIN.
For example, the two queries below return the same result.
SELECT Food, Drink from foods, drinks
WHERE foods.id = drinks.id
SELECT Food, Drink from foods
INNER JOIN drinks ON foods.id = drinks.id
order by Food
Food Drink
Chicken Tacos Iced Tea
Margherita Pizza Cold Brew Coffee
Chocolate Brownie Sparking Lime WaterWhat is the Difference between SQL CROSS APPLY and CROSS JOIN?
Although the above two names look similar, they return distinct results. As we all know, the CROSS JOIN, let me explain the CROSS APPLY. It returns the records in two tables side by side. For example, the query below returns three rows side by side.
SELECT Food, FPrice, d.Drink, d.DPrice
FROM foods
CROSS APPLY
(SELECT Drink, DPrice FROM drinks WHERE foods.id = drinks.id) AS d
Food FPrice Drink DPrice
Chicken Tacos 7.00 Iced Tea 4.50
Margherita Pizza 11.00 Cold Brew Coffee 5.00
Chocolate Brownie 4.50 Sparking Lime Water 3.50How to handle NULL Values in SQL CROSS JOIN?
If there are any NULL values in any of the two tables, the result set will be the same. It will perform every combination of other table column records against the NULL value and return the result.
For instance, if any one of the three food item values is NULL, the CROSS JOIN still returns 9 rows. Each NULL value is combined with three drinks. If you want to replace NULL with any default or meaningful data, use the COALESCE Function.
Can we use the MERGE and HASH option with CROSS JOIN?
No. The CROSS JOIN won’t allow the use of the MERGE JOIN or HASH JOIN options. So, when you use them, it returns an error.
The following two queries throw an error.
SELECT * FROM foods
CROSS JOIN drinks
OPTION (MERGE JOIN );
SELECT * FROM foods
CROSS JOIN drinks
OPTION (HASH JOIN );
Message
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.