Understanding SQL CROSS JOIN (Practical Examples Included)

CROSS JOIN Introduction

What is a CROSS JOIN in SQL?

In SQL, CROSS JOINs are used to combine each row of one table with each row of another table, and return the Cartesian product of the sets of rows from the tables that are joined.

When to use the CROSS JOIN?

The CROSS JOIN query in SQL is used to generate all combinations of records in two tables. For example, you have two columns: size and color, and you need a result set to display all the possible paired combinations of those—that's where the CROSS JOIN will come in handy.

CROSS JOIN in SQL Server Syntax of CROSS JOIN in SQL between two tables

The syntax for the CROSS JOIN is as follows:

SELECT      [column names]
FROM        [TableA]
CROSS JOIN  [TableB]
                                    

Enjoying CROSS JOINs with

SQL Complete

What is CROSS JOIN in SQL Server with example in SQL Complete

Though the syntax for the CROSS JOIN query in SQL seems quite simple and straightforward, you need to be cautious with it. First, CROSS JOINs can potentially return huge result sets that are difficult to manage and analyze. Second, you must remember the exact names of the tables and columns to run the query.

That's where SQL Complete comes so useful. Its advanced code completion and context-sensitive hints let you write faster and cleaner code. And with the Column Picker functionality, you no longer need to worry about memorizing column names—just select the required columns from the suggestion list, which can be filtered for user convenience.

In this tutorial, we will take a close look at CROSS JOINs in SQL, various aspect of their usage, and how to facilitate the work with JOINs using SQL Complete.


dbForge SQL Complete

Handle even the most complex JOINs with SQL Complete

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. For this, we run the following query:

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—in fact, the number of rows in the first table is multiplied by the number of rows in the second table: 3х3=9.

What is CROSS JOIN in SQL Server with example

Use CROSS JOIN to join three tables in SQL Server

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

Let's consider the following example. Assume, that now we need to get all the combinations of not only car models and colors, but also tyres that can go with those cars.

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

And here is the results set consisting of 27 rows. As you remember, we have 3 car models, 3 car colors, and now we add 3 tyres manufacturers. The CROSS JOIN returns all the possible combinations of those: 3x3x3=27.

How to cross join three tables in SQL Server with the help of SQL Complete

Use CROSS JOIN to generate a report

You can use CROSS JOIN to retrieve database data and display it in the format convenient for analysis and reporting.

Imagine you have the Staff table that includes the following columns: StaffID, Department, Shift_name, Start_time, End_time.

We want to generate a report that shows all possible shifts for the Maintenance department. For this, we run the SELECT statement with the CROSS JOIN clause and apply filtering using WHERE.

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
                                    
How to use CROSS JOINs for reporting in SQL Server

Use CROSS JOIN to generate a large set of data

CROSS JOINs can potentially generate big data sets and because of that should be used wisely.

Suppose that our Car_models and Color_names columns have 1000 rows each. In this case, when we cross join them in a query, it will return the result set as big as one million rows.

Lots of experts recommend avoiding CROSS JOINs because of performance issues. The best option would be to pre-aggregate data before using a CROSS JOIN if it is really needed.

Ways 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
CROSS JOINs generate large data sets

SQL execution plan of the CROSS JOIN query

Let's compare two queries: the one with CROSS JOIN and its alternative.

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 the INNER JOIN

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

The queries are quite heavy and return one million rows. As you can see from their execution plans, INNER JOIN is significantly faster and less costly.

CROSS JOIN alternative execution plans

FAQ

With SQL Complete, you GET

  • Code snippets for JOIN clauses
  • Context-based prompts for table and column names
  • Navigation between the CASE and END keywords
  • Highlighting of matching keyword pairs
  • Quick info about database objects
  • Instant code formatter with built-in formatting profiles

SQL Complete

Advanced solution for SQL database development, management, and administration

Try dbForge SQL Complete and double the speed of your SQL coding! Try now