SQL Server Essentials: Using SQL Cross Joins

DevOps code

SQL CROSS JOIN queries are used to generate a paired combination of each row of the first table with each row of the second table. This kind of result is called a Cartesian product. In this article, I’ll show you how to use an SQL CROSS JOIN.

How does an SQL Cross Join work?

The CROSS JOIN is a somewhat uncommon type of SQL JOIN. As I explained earlier, an SQL CROSS JOIN query gives a Cartesian product, where the number of rows in the result set is the product of the number of rows of the two tables. In other words, it produces a result set where each row from the first table is combined with every row from the second table, resulting in all possible combinations.

An SQL CROSS JOIN is often used without a WHERE clause: In that case, it’s like an INNER JOIN where every condition evaluates to ‘true’. However, if a WHERE clause is used with an SQL CROSS JOIN query, it will act like a standard INNER JOIN.

You can see an overview of how an SQL CROSS JOIN query works in the following figure

How an SQL CROSS JOIN query works
How an SQL CROSS JOIN query works (Image credit: Petri/Michael Otey)

How to use an SQL Cross Join

You can use a SQL CROSS JOIN when you want to generate all possible combinations between two or more tables without any specific condition for the join. But as you might guess, this query can potentially generate a large number of rows in the result set, especially when dealing with large tables.

The syntax you need to use is fairly simple: You just add the CROSS JOIN keyword to your SELECT statement along with the table names that you want to join. The following listing shows an example using the Customers and Orders tables from the Northwind sample database.

USE Northwind
SELECT Customers.CustomerID, Customers.CompanyName, Orders.*
 FROM Customers CROSS JOIN Orders

You can see the result of this query in the following figure.

SQL Cross Join example with the Northwind sample database
SQL Cross Join example with the Northwind sample database (Image credit: Petri/Michael Reinders)

In this example, the ‘CustomerID’ and ‘CompanyName’ columns from the Customers table and all of the columns from the Orders table are joined using the CROSS JOIN clause.

Why you should use SQL CROSS JOINS with caution

In this tutorial, you learned what a SQL CROSS JOIN is and saw an example of one using the Northwind database. However, you should be aware that this type of query can be computationally expensive. It can result in performance issues and can also result in a large number of rows. As a result, my advice is to use them with caution.