What is CROSS JOIN in SQL?

SQL CROSS JOIN explained with a practical example

Suppose, we have two database tables: Cars, listing car models and their prices, and Colors, listing color names and extra prices for those colors. We need to get all possible combinations of cars and colors. CROSS JOIN will be the solution:

SELECT
   c.Car_model
 ,c1.Color_name
FROM Cars c
CROSS JOIN Colors c1

We have three car models and three colors. The CROSS JOIN query returns nine results — the number of rows in the first table is multiplied by the number of rows in the second table: 3×3=9.

Use CROSS JOIN to join three tables in SQL Server

You can use the CROSS JOIN on as many tables as you want.

Assume, that we need to get all the combinations of not only car models and colors, but also tires that can go with those cars.

SELECT
  c.Car_model
 ,c1.Color_name
 ,t.Tire_manufacturer
FROM Cars c
CROSS JOIN Colors c1
CROSS JOIN Tires t

We have 3 car models, 3 car colors, and now we add 3 tire manufacturers. The result set consists of 27 rows as CROSS JOIN returns all the possible combinations of those: 3×3×3=27.

Use CROSS JOIN to generate a report

CROSS JOIN can help you retrieve database data and display it in the convenient format.

Assume we have the Staff table with the StaffID, Department, Shift_name, Start_time, and End_time columns. We want a report showing all possible shifts for the Maintenance department.

The CROSS JOIN clause will help us retrieve the information. We also filter the results using the WHERE condition.

SELECT s.Department, s1.Shift_name, s1.Start_Time, s1.End_Time
FROM Staff s
CROSS JOIN Staff s1
WHERE s.Department = 'Maintenance'
ORDER BY s1.Start_Time

Use CROSS JOIN to generate a large set of data

CROSS JOINs can produce very large result sets, so they should be used carefully.

For example, if the Car_models and Color_names tables each contain 1,000 rows, a CROSS JOIN between them would generate a result set of 1,000 × 1,000 = 1,000,000 rows.

Because of the potential performance impact, many experts recommend avoiding CROSS JOIN when possible. If it is truly necessary, pre-aggregate or filter the data beforehand to reduce the size of the result set.

How to avoid performance issues associated with CROSS JOINs:

• Use another JOIN (INNER/LEFT/RIGHT) with 2 ON conditions
• Use the GROUP BY clause to pre-aggregate data

SQL execution plan of the CROSS JOIN query

Let's compare two queries: one using a CROSS JOIN and the other an INNER JOIN.

Query 1: With CROSS JOIN

SELECT p.Product_name, s.Store_address
FROM Products p
CROSS JOIN Stores s;

Query 2: CROSS JOIN is replaced with INNER JOIN

SELECT p.Product_name, s.Store_address
FROM Products p
INNER JOIN Stores s ON p.ProductID=s.StoreID;

Both queries are fairly heavy and return one million rows. However, as shown in their execution plans, the INNER JOIN is noticeably faster and more efficient in terms of resource cost.