SQL Server: Joins - Cartesian Product

 SQL Server     sbo     Updated on Jul 07, 2013     VIKRAM     913   
First create two tables Employee, Department.
Step 1: Create 'Employee' table here:

create table Employee(EmpId int primary key, EmpName varchar(30), DeptId int)

Step 2: Insert data into Employee table.
insert into Employee values(900,'AAA',501)
insert into Employee values(901,'BBB',502)
select * from Employee
Output:
EmpId EmpName DeptId
900 AAA 501
901 BBB 502

 

Step 3: Create 'Department' table here:

create table Department(DeptCode varchar(5) primary key, DeptName varchar(18))

Step 4: Inser data into Department table.

insert into Department values('CSE','This is CSE Department')

insert into Department values('ECE','This is ECE Department')

insert into Department values('EEE','This is EEE Department')

insert into Department values('MECH','This is Mechanical Department')

insert into Department values('IT','This is IT Department')

select * from Department

Output:

DeptCode DeptDescription
CSE This is CSE Department
ECE This is ECE Department
EEE This is EEE Department
IT This is IT Department
MECH This is Mechanical Department

 

Catresian Product:


Syntax:

select * from <Table1>, <Table2>

Example:

select * from Employee, Department

Output:

EmpId EmpName DeptId DeptCode DeptDescription
900 AAA 501 CSE This is CSE Department
900 AAA 501 ECE This is ECE Department
900 AAA 501 EEE This is EEE Department
900 AAA 501 IT This is IT Department
900 AAA 501 MECH This is Mechanical Department
901 BBB 502 CSE This is CSE Department
901 BBB 502 ECE This is ECE Department
901 BBB 502 EEE This is EEE Department
901 BBB 502 IT This is IT Department
901 BBB 502 MECH This is Mechanical Department

 

Note: 

If Table1 has 2 rows and Table2 has 5 rows then the cartesia prodcut of Table1 and Table2 is 10 rows. i.e., (Table1 rows * Table2 rows) = 10.

  Related Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Cross Joins
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
  Recent Articles
SQL Server: Non Equi Join
SQL Server: Equi Join
SQL Server: Cross Joins
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