Tutorial Gateway

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.

TIP: Cross Join in SQL does not require any common column to join two table.

SQL Cross Join Syntax

The basic syntax of the Cross Join in SQL Server is as shown below:

-- 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

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, Cross join displays the Cartesian product of two tables. It means, every record in Table A is combining with every record Table B

In this article we will show you, How to write Cross Join in SQL Server. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is as shown below:

SQL CROSS JOIN Example

Data present in the Department Table is:

SQL CROSS JOIN

SQL Cross Join – Using Select *

The following SQL Query will display all the existing columns in employee, and Department tables

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

If you observe the below 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 to select individual column names. Please avoid SELECT * Statement in Cross Join to avoid unwanted columns like id, DeptID, id etc.

SQL Cross Join Example – Select Few Columns

As we said before, please select the required columns after the SELECT Statement to avoid unwanted columns. The following SQL 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]

OUTPUT

SQL CROSS JOIN 4

SQL Cross Join – Ambiguous Columns

Above Transact query will work perfectly, if column names from both tables (Employee, and Department) are different like above. What will happens if they had same column names in both the tables? Well, with the above specified approach, you will end up with an error. Let us see how to resolve the issue.

Before we get into the solution, let me show you one practical example. As you can see, we are using the above query but we added id from department table as an additional column.

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

You can see from the below screenshot, it is throwing an error : Ambiguous column name id. This is because, id column is present in both Employee, and department table. And SQL Server don’t know which column you are asking it to retrieve.

SQL CROSS JOIN 5

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

We can simply write the above 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

OUTPUT

SQL CROSS JOIN 6

SQL Cross Join Example – Avoid Join keyword

In SQL Server, you can also avoid the CROSS JOIN keyword to get the result. Following query will show you the same.

-- 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

OUTPUT

SQL CROSS JOIN 1

Using Where Clause in SQL Cross Join

The SQL Server allows us to use the Where Clause to restrict the number of rows returned by the Cross Join. In this example, we will 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'

OUTPUT

SQL CROSS JOIN 2

SQL Cross Join – Order By Clause

The SQL Server 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'

OUTPUT

SQL CROSS JOIN 7

Thank You for Visiting Our Blog

Placed Under: SQL