SQL Server: Cross Joins

 SQL Server     sbo     Updated on Nov 17, 2013     VIKRAM     1196   

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
  Related Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Full Join (or) Full Outer Join
SQL Server: Joins
SQL Server: Right Join (or) Right Outer Join
SQL Server: Left Join (or) Left Outer Join
SQL Server: Inner Join (or) Join
SQL Server: How to get current week dates
SQL Server: Joins - Cartesian Product
  Recent Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Full Join (or) Full Outer Join
SQL Server: Joins
SQL Server: Right Join (or) Right Outer Join
SQL Server: Left Join (or) Left Outer Join
SQL Server: Inner Join (or) Join
SQL Server: How to get current week dates
SQL Server: Joins - Cartesian Product