SQL CROSS JOIN

The SQL Server Cross Join returns the Cartesian product of both tables. Cartesian product means the Number of Rows present in Table 1 Multiplied by the Number of Rows present in Table 2.

The SQL Cross Join does not require any common column to join two tables. Let us see the visual representation of the Cartesian Join for a better understanding.

Cartesian product diagram

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

SQL Server Cross Join Syntax

The syntax of the Cross Join is shown below.

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 example, we use two tables (Employee and Department). Data present in the Employee

Left Table Data

Data present in the SQL Server Department is:

Right Table data

SQL Cross Join Select * Example

The following query will display all the existing employee & Department table columns.

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 eight rows in the Department table.

SQL CROSS JOIN Select * 3

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

Select a Few Columns Example

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
FROM [Employee]
   CROSS JOIN [Department]
SQL CROSS JOIN to Select few Columns 4

SQL Cross Join Ambiguous Columns

The Above Transact query will work flawlessly if column names from both tables (Employee and Department) are different like above. But what happens if they had the same column names in both 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.

SELECT [FirstName]
      ,[LastName]
      ,id
      ,[DepartmentName]
FROM [Employee]
   CROSS JOIN [Department]

As you see, Sql Server Cross Join is throwing an error: Ambiguous column name id. The id column is present on both Employee and department tables. And the Server doesn’t know which column you are asking it to retrieve.

Ambiguous Column Name Error 5

To resolve these issues, you must use the table name before the column name. For example, the following query uses the ALIAS table name before the column names. By this approach, we can inform the Server that we are looking for the id column belonging to the department table.

We can write the above SQL Cross Join query to solve ambiguous column error as:

SELECT Emp.[FirstName]
      ,Emp.[LastName]
      ,Dept.id
      ,Dept.[DepartmentName]
FROM [Employee] AS Emp
CROSS JOIN
     [Department] AS Dept
Remove Ambiguous Column Error 6

You can also avoid the keyword to get the result.

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
Without Keyword 1

SQL Cross Join Where Clause

We can use the Where Clause to restrict the number of rows returned by this. So, here, we use that WHERE Clause along with this.

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 Where Clause 2

Order By Clause Example

It allows us to use Order By Clause to rearrange the order of the records.

SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
     [Department] AS Dept
ORDER BY [FirstName], [LastName],  [DepartmentName]
Order By Clause 7
Categories SQL