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
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.
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.
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
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
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
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