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.



E_Salary


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

 


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


 

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



 

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


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



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



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

Leave Comment