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 Join- When 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