Types of Joins in SQL

A JOIN is a means for combining fields from two tables by using values common to each.
An SQL JOIN clause is used to combine records from two or more tables while querying a database.

All subsequent explanations on join types in this article make use of the following two tables.

 

 

---

 

 

1. Inner Join :
   1. Equi Join
   2. Natural Join
   3. Self Join
2. Outer Join :
   1. Left Outer Join
   2. Right Outer Join
   3. Full Outer Join
3. Cross/Cartesian-Product Join

---

 
1. Inner Join :
 

• An inner join creates a new result set by combining column values of two tables (A and B) based upon the join-predicate(ie condition).
• The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.

---

 
1.1 Equi Join :
 

• An equi-join, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate.

Eg :

Purpose :
To list employees & the departments they belong to.

Method 1 :

 
Method 2 :

Result :

Employee.LastName	Employee.DeptID	Dept.DeptName	Dept.DeptID
Robinson	34	Clerical	34
Jones	33	Engineering	33
Smith	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31

 

---

 
1.2 Natural Join :
 

• The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables.

Eg :

Purpose :
To list employees & the departments they belong to.

Method 1 :

 
Result :

DeptID	Employee.LastName	Dept.DeptName
34	Smith	Clerical
33	Jones	Engineering
34	Robinson	Clerical
33	Steinberg	Engineering
31	Rafferty	Sales

 

---

 
1.3 Self Join :
 

• Similar in syntax with INNER Join except that A self-join is joining a table to itself.

Eg :

Purpose :
To list employees belonging to the same department.

Method 1 :

 
Result :

DeptID	LastName
33	Jones
33	Steinberg
34	Smith
34	Robinson

 

---

 
2. Outer Join :
 

• An outer join does not require each record in the two joined tables to have a matching record.
• The joined table retains each record—even if no other matching record exists.

---

 
2.1 Left Outer Join :
 

• Left outer join(or simply left join) returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
• If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.

Eg :

Purpose :
To show ALL employees of the company along with the departments they are in.

Method :

Result :

Employee.LastName	Employee.DeptID	Dept.DeptName	Dept.DeptID
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
Jasper	NULL	NULL	NULL
Steinberg	33	Engineering	33

 

---

 
2.2 Right Outer Join :
 

• A Right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed.
• A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
• SQLite does not support right join. Left Join can be used as an alternative here by exchanging the table positions.

Eg :

Purpose :
To show ALL departments alongwith the employees in them.

Method :

Result :

Employee.LastName	Employee.DeptID	Dept.DeptName	Dept.DeptID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

 

---

 
2.3 Full Outer Join :
 

• A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
• Some database systems (like MySQL) do not support this functionality directly, but they can emulate it through the use of left and right outer joins and unions.

Eg :

Purpose :
To show ALL employees & ALL departments.

Method 1 :

 
Method 2 : Implementing Full Outer Join using Left,Right Joins :

Result :

Employee.LastName	Employee.DeptID	Dept.DeptName	Dept.DeptID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Jasper	NULL	NULL	NULL
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

 

---

 
3. Cross/Cartesian-Product Join :
 

• A cross join returns the cartesian product of the sets of records from the two joined tables.
• The cross join does not apply any predicate to filter records from the joined table.
• Programmers can further filter the results of a cross join by using a WHERE clause.

Eg 1:

 
Eg 2:

 

---

Ref :
http://en.wikipedia.org/wiki/Join_(SQL)

 

---