Mastering Cross Join in SQL Server: A Complete Guide

CROSS JOIN

CROSS JOIN, produces the cartesian product of the 2 tables involved in the join. For example, in the Employees table we have 10 rows and in the Departments table we have 4 rows. So, a cross join between these 2 tables produces 40 rows. Cross Join shouldn't have ON clause. 

SELECT ColumnList
FROM LeftTableName
JOIN_TYPE RightTableName
ON JoinCondition

Code:

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
CROSS JOIN tblDepartment

Code: SQL Script to create tblEmployee and tblDepartment tables

  
Create table tblDepartment
(
     ID int primary key,
     DepartmentName nvarchar(50),
     Location nvarchar(50),
     DepartmentHead nvarchar(50)
)
Go

Insert into tblDepartment values (1, 'IT', 'London', 'Rick')
Insert into tblDepartment values (2, 'Payroll', 'Delhi', 'Ron')
Insert into tblDepartment values (3, 'HR', 'New York', 'Christie')
Insert into tblDepartment 
values (4, 'Other Department', 'Sydney', 'Cindrella')
Go

Create table tblEmployee
(
     ID int primary key,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int,
     DepartmentId int foreign key references tblDepartment(Id)
)
Go

Insert into tblEmployee values (1, 'Azam', 'Male', 4000, 1)
Insert into tblEmployee values (2, 'Roma', 'Female', 3000, 3)
Insert into tblEmployee values (3, 'Inza', 'Male', 3500, 1)
Insert into tblEmployee values (4, 'Jaman', 'Male', 4500, 2)
Insert into tblEmployee values (5, 'Samser', 'Male', 2800, 2)
Insert into tblEmployee values (6, 'Kamran', 'Male', 7000, 1)
Insert into tblEmployee values (7, 'Sara', 'Female', 4800, 3)
Insert into tblEmployee values (8, 'Valarie', 'Female', 5500, 1)
Insert into tblEmployee values (9, 'Rumman', 'Male', 6500, NULL)
Insert into tblEmployee values (10, 'Russell', 'Male', 8800, NULL)
Go