Cross Join in SQL Server
Table of Content:
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