Difference Between Inner Join,Outer Join, Self Join and Cartesian Product

Joins :- If you see in general term join is to combine two or more tables in a manner that you can get your desired output. It is called join.

There are only three types of joins

1) Equi Join
2) Non Equi Join
3) Cross Jon or Cartesian Product.

Equi Join :-
When you use equal to(=) operator in where clause it is called Equi join

Ex.
Select EmpName,Salary from Employee, SalaryRange where Employee.Salary =SalaryRange.MinSalary.

Non Equi Join :-
When you use any operator(!=,>,<,Between) other than equal to, it is called Non-Equi join. Ex select EmpName,Salary from Employee, SalaryRange where Employee.Salary between SalaryRange.MinSalary and SalaryRange.MaxSalary Cartesian Product :- If do not use any where condition in the join query than it is called Cartesian Product or Cross Join.

Further Equi join and Non Equi Join can be divided into following categories.

1)Inner Join
2)Outer Join
3)Self Join

Inner join :- An inner join (sometimes called a simple join ) is a
join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins :- An outer join extends the result of a simple join. An
outer join returns all rows that satisfy the join condition and also returns non matching rows based on Outer join type.


Further Outer Join can be divided into following categories

1) Left Outer Join :- All Matching Records + All Non Matching Records from Left Table
2) Right Outer Join :- All Matching Records + All Non Matching Records from Right Table
3) Full Outer Join :- All Matching Records + All Non Matching Records from both Tables


Self Join :- When we join a table to itself it is called Self Join.

Examples :-

Suppose we have two tables Employee and Manager with following data

Employee    
Id     Name
1      Aslam
2      Ammar
3      Azam


Manager    
Id     Name
1      Amir
2      Asim
4      Arif

Inner Join
All Matching Records
select a.Id,a.Name from Employee a INNER JOIN Manager b on a.Id = b.Id;

Output :
Id     Name
1      Aslam
2      Ammar

Left Outer Join
All Matching Records + All Non Matching Records from Left Table

Select a.Id,a.Name from Employee a LEFT OUTER JOIN Manager b on a.Id = b.Id;

Output :
Id     Name
1      Aslam
2      Ammar
3      Azam

Right Outer Join
All Matching Records + All Non Matching Records from Right Table
select b.Id,b.Name from Employee a RIGHT OUTER JOIN Manager b on a.a = b.b;

Output :
Id     Name
1         Aslam
2         Ammar
3         Azam
4         Arif

Full Outer Join
All Matching Records + All Non Matching Records from Both Table
Select * from Employee a FULL OUTER JOIN Manager b on a.I = b.Id;

Output :
Id     Name         Id      Name
1        Aslam         1       Amir
2        Ammar         2       Asim
3        Azam          NULL    NULL
NULL     NULL          4       Arif

Self Join
A join from a table to itself is called self join.
Suppose you have a table Name Employee which contain following data

Id      Name         ManagerId   
1       Asim          3
2       Arif          3
3       Asif          0

In this table every employee has a Manager But top one has no Manager so its manager id is zero

We want all Employee name and their Manager Name

Select a.Name as EmpName,b.Name as ManagerName from Employee a, Employee b where a.ManagerId= b.Id and b.ManagerId !=0

 Output :
EmpName       ManagerName
Asmim           Asif
Arif            Asif

Cartesian Product
When you join two or more tables without any condition, it is called Cartesian product or Cross Join.

Select a.Id,b.Id from Employee a,Manager b

Employee    Manager  
-            -
1            1
1            2
1            4
2            1
2            2
2            4
3            1
3            2
3            4