What is Cross Join in SQL Server

Introduction:

In this article i will explain what is Cross Join in SQL Server.

Description:

In previous articles i explained what are joins, what is INNER JOIN in SQL Server, what is LEFT JOIN (or) LEFT OUTER JOIN in SQL Server,  what is RIGHT JOIN (or) RIGHT OUTER JOIN, and what is FULL JOIN or FULL OUTER JOIN in SQL Server. Now i will explain what is Cross Join in SQL Server.

CROSS JOIN:

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

Example:

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from          Employee E
cross join    Department D

The result set contains 15 rows (Employee has 3 rows and Department has 5; 3 multiplied by 5 equals 15).

Note: 

However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from          Employee E
cross join    Department D
where         E.DepartmentID = D.DepartmentID

(or)

select        E.EmployeeID, E.EmployeeName, E.Email, E.DepartmentID, D.DepartmentName
from        Employee E
inner join    Department D
on            E.DepartmentID = D.DepartmentID