SQL CROSS JOIN Chart

SQL CROSS JOIN

by

The SQL Cross Join returns the Cartesian product of both the tables. Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2. The SQL Server Cross Join does not require any common column to join two tables. Let us see the visual representation of the Cross Join for better understanding.

SQL CROSS JOIN Chart

From the above screenshot, you can easily understand that SQL Server Cross join displays the Cartesian product of two tables. It means, every record in Table A is combining with every record Table B

SQL Cross Join Syntax

The syntax of the Cross Join in SQL Server is

-- SQL Server Cross Join Syntax
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
 CROSS JOIN
     Table2 

--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1, Table2

For this SQL Server Cross Join example, we use two tables (Employee and Department). Data present in the Employee Table

SQL CROSS JOIN Example

Data present in the SQL Server Department Table is:

SQL CROSS JOIN

SQL Cross Join Select * Example

The following cross Join query will display all the existing columns in an employee & Department tables

-- SQL Server Cross Join Example
USE SQLTEST
GO
SELECT * FROM [Employee]
   CROSS JOIN [Department];

If you observe the below Join screenshot, It is displaying 120 records. It means 15 rows from the Employee multiplies by 8 rows in the Department table

SQL CROSS JOIN 3

NOTE: I suggest you select individual column names. Please avoid SELECT * Statement in Cross Join to avoid unwanted columns like id, DeptID, id, etc.

SQL Cross Join Select Few Columns Example

As we said before, please select the required columns after the SELECT Statement to avoid unwanted columns. The following cross join query will display the Cartesian product of the columns present in Employee and Department tables.

-- SQL Server Cross Join Example
USE SQLTEST
GO
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
FROM [Employee]
   CROSS JOIN [Department]
SQL CROSS JOIN 4

SQL Cross Join Ambiguous Columns

Above Transact query will work flawlessly if column names from both tables (Employee and Department) are different like above. What happens if they had the same column names in both the tables? Well, with the above-specified approach, you will end up with an error.

Let me show you one practical example. We added id from the department table as an additional column to the above query.

-- SQL Server Cross Join Example
USE SQLTEST
GO
SELECT [FirstName]
      ,[LastName]
      ,id
      ,[DepartmentName]
FROM [Employee]
   CROSS JOIN [Department]

As you see, it is throwing an error: Ambiguous column name id. It is because the id column is present in both Employee and department table. And SQL Server doesn’t know which column you are asking it to retrieve.

SQL CROSS JOIN 5

To resolve these kinds of issues, you always have to use the table name before the column name. The following Cross Join query is using the ALIAS table name before the column names. By this approach, we can inform the Server that we are looking for id column belonging to the department table.

We can write the above cross join query as:

-- SQL Server Cross Join Example
SELECT Emp.[FirstName]
      ,Emp.[LastName]
      ,Dept.id
      ,Dept.[DepartmentName]
FROM [Employee] AS Emp
CROSS JOIN
     [Department] AS Dept
SQL CROSS JOIN 6

Cross Join without Join keyword

You can also avoid the CROSS JOIN keyword to get the result.

-- SQL Server Cross Join Example
SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
CROSS JOIN
     [Department] AS Dept

--OR We Can simply Write it as
SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
     [Department] AS Dept
SQL CROSS JOIN 1

SQL Cross Join Where Clause

We can use the Where Clause along with cross join in restricting the number of rows returned by the Cross Join. Here, we use that WHERE Clause along with the Cross Join.

-- SQL Server Cross Join Example
SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
     [Department] AS Dept
WHERE Dept.[DepartmentName] = 'Software Developer'
SQL CROSS JOIN 2

Cross Join Order By Clause Example

The cross join allows us to use Order By Clause to rearrange the order of the records.

-- SQL Server Cross Join Example
SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
     [Department] AS Dept
WHERE Dept.[DepartmentName] = 'Software Developer'
SQL CROSS JOIN 7