Join is use to combine more than two table to find result as per our need.Join is possible when there is one match in joining table.

Types of join

1-Inner Join

2-Left Join

3-Right Join

4-Full Join

5-Self Join

6-Cartesian Join or Cross Join

Inner Join Inner Join is very important Join most of the time we use inner join.When we join two or more table with inner join it returns all the rows when there is a match in both the table.

We have two table Employee and I have inserted few records in it.


Types Of Join in Sql Server

E_Salary

Types Of Join in Sql Server

select
Employee.Name,Employee.Department,E_Salary.Salary
from Employee
inner join
E_Salary
on
Employee.Id=E_Salary.S_Id

 

Types Of Join in Sql Server

Left Join-When we Join more than two table with left join it gives all record from the left table and matching record form the right table.It means when right table matches null record or 0 record then right table return 0 record.

select
Employee.Name,Employee.Department,E_Salary.Salary
from Employee
left join
E_Salary
on
Employee.Id=E_Salary.S_Id

 

Output

Types Of Join in Sql Server

 

Right Join-When we join more than two tables with right join.It gives all record from the right table and matching from the left table.Right Join is the opposite of Left Join.If there is 0 records in the left table then left join return null records.

 

select
Employee.Name,Employee.Department,E_Salary.Salary
from Employee
Right join
E_Salary
on
Employee.Id=E_Salary.S_Id

 

Output

Types Of Join in Sql Server


 

Full Join-When we use Full Join for combining record .It return all the record from the left table and all the records from the right table and give null  for missing records.

select
Employee.Name,Employee.Department,E_Salary.Salary
from
Employee
full join
E_Salary
on
Employee.Id=E_Salary.S_Id

Output

Types Of Join in Sql Server

Self Join

Self Join is use to join the table by itself by making its alias just like virtual table.

select a.Name,b.Department
from
Employee a,Employee b
where
a.Id=b.Id

 

Output

Types Of Join in Sql Server


Cartesian JoinWhen we join two or more table with cross join then it return cross product of the tables.It is also known as cross join.

Suppose you have table1 with m rows and n columns then the result o cross join is m*n rows.

SELECT * 
FROM Employee
CROSS JOIN E_Salary;


Output


Types Of Join in Sql Server

  Modified On Nov-29-2017 04:54:58 PM

Leave Comment