Understanding SQL CROSS JOIN (Practical Examples Included)

Q: Is Cross JOIN the same as FULL OUTER JOIN?

QL CROSS JOIN vs FULL OUTER JOIN. As a result of the CROSS JOIN statement, a Cartesian Product is produced, and the query returns all possible combinations of all rows in two tables. CROSS JOINs don't have ON clauses as everything is joined with everything. FULL OUTER JOIN is a combination of LEFT OUTER and the RIGHT OUTER JOINs. FULL OUTER JOIN returns those rows in two tables that match the WHERE clause, and shows null values for the rows the ON condition isn't met for.

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.

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.

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

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.

FAQ

What is the difference between Cartesian JOIN and CROSS JOIN?

When you run a statement containing the CROSS JOIN clause, you get a Cartesian Product—each row in the first table is paired with every row in the second table.

Is Cross JOIN the same as FULL OUTER JOIN?

SQL CROSS JOIN vs FULL OUTER JOIN. As a result of the CROSS JOIN statement, a Cartesian Product is produced, and the query returns all possible combinations of all rows in two tables. CROSS JOINs don't have ON clauses as everything is joined with everything. FULL OUTER JOIN is a combination of LEFT OUTER and the RIGHT OUTER JOINs. FULL OUTER JOIN returns those rows in two tables that match the WHERE clause, and shows null values for the rows the ON condition isn't met for.

What is a CROSS JOIN and NATURAL JOIN in SQL?

The difference between NATURAL JOIN and CROSS JOIN in SQL is quite straightforward. A NATURAL JOIN joins two tables implicitly, based on the common columns in the two tables that are joined. Common columns are the columns that have the same name and datatype. A NATURAL JOIN can be an INNER JOIN, a LEFT OUTER JOIN, or a RIGHT OUTER JOIN. Meanwhile CROSS JOIN produces a result set that contains all the attributes of both tables, including duplicate and common columns.

Is CROSS JOIN the same as a CROSS APPLY?

Speaking of SQL CROSS JOIN vs CROSS APPLY, one should mention that CROSS APPLY retrieves all the records from the table where there are corresponding matching rows in the output returned by the table-valued function. The table-valued function acts as the right input and the outer table expression acts as the left input. In fact, CROSS APPLY works similarly to row-by-row INNER JOIN.

What is the difference between JOIN and INNER JOIN?

In case you don't specify the type of JOIN when using the JOIN operator in your syntax, INNER JOIN will be used by default. However, for the purpose of code readability and clarity, it is better to specify the JOIN types, especially if the query contains different SQL JOINs.

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

Success stories

Understanding SQL CROSS JOIN (Practical Examples Included)

CROSS JOIN Introduction

Enjoying CROSS JOINs with
SQL Complete

dbForge SQL Complete

Handle even the most complex JOINs with SQL Complete

SQL CROSS JOIN explained with a practical example

Use CROSS JOIN to join three tables in SQL Server

Use CROSS JOIN to generate a report

Use CROSS JOIN to generate a large set of data

SQL execution plan of the CROSS JOIN query

FAQ

Contract Resources (NZ) Ltd

Elements Financial

Broughton Software

Svitla Systems

SQL Complete

Advanced solution for SQL database development, management, and administration

Understanding SQL CROSS JOIN (Practical Examples Included)

CROSS JOIN Introduction

Enjoying CROSS JOINs with SQL Complete

dbForge SQL Complete

Handle even the most complex JOINs with SQL Complete

SQL CROSS JOIN explained with a practical example

Use CROSS JOIN to join three tables in SQL Server

Use CROSS JOIN to generate a report

Use CROSS JOIN to generate a large set of data

SQL execution plan of the CROSS JOIN query

FAQ

Contract Resources (NZ) Ltd

Elements Financial

Broughton Software

Svitla Systems

SQL Complete

Advanced solution for SQL database development, management, and administration

Enjoying CROSS JOINs with
SQL Complete