SQL Server Cross Join

Summary: in this tutorial, you will learn how to use the SQL Server CROSS JOIN to join two or more tables.

Introduction to the SQL Server CROSS JOIN clause #

A cross join allows you to combine rows from the first table with every row of the second table. In other words, it returns the Cartesian product of two tables.

Here’s the basic syntax for a cross join:

SELECT
  select_list
FROM
  T1
CROSS JOIN T2;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• T1 and T2 are the tables that you want to perform a cross join.

Unlike other join types such as INNER JOIN or LEFT JOIN, the cross join does not require a join condition.

If T1 table has n rows and T2 table has m rows, the cross join will create a result set with nxm rows.

For example, if both tables T1 and T2 have 1000 rows, the cross join will return a result set with 1,000,000 rows.

Because a cross join may create a large number of rows in the result set, you should use it carefully to avoid performance issues.

SQL Server Cross Join illustration #

Suppose the T1 table contains three rows 1, 2, and 3 and the T2 table contains three rows A, B, and C.

The CROSS JOIN combines each row from the first table (T1) with every row in the second table (T2), creating a new row for each combination. It repeats this process for each subsequent row in the first table (T1) and so on.