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.

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

 

 


 

Types of Joins :

 

  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 :

SELECT *
FROM employee
INNER JOIN dept
ON employee.deptID = dept.deptID

 
Method 2 :

SELECT *
FROM employee,dept
WHERE employee.deptID = dept.deptID

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 :

SELECT *
FROM employee
NATURAL JOIN dept

 
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 :

SELECT a.DeptID,a.LastName
FROM employee a
INNER JOIN employee b ON (a.deptid = b.deptid AND a.lastname!=b.lastname)
ORDER BY a.deptid

 
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 :

SELECT *
FROM employee LEFT OUTER JOIN dept
ON employee.DeptID = dept.DeptID

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 :

SELECT *
FROM employee RIGHT OUTER JOIN dept
ON employee.deptID = dept.deptID

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 :

SELECT *
FROM employee
FULL OUTER JOIN dept
ON employee.deptID = dept.deptID

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

SELECT *
FROM employee
LEFT JOIN dept
ON employee.deptID = dept.deptID
UNION
SELECT *
FROM employee
RIGHT JOIN dept
ON employee.deptID = dept.deptID
WHERE employee.deptID IS NULL

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:

SELECT *
FROM employee CROSS JOIN dept

 
Eg 2:

SELECT *
FROM employee, dept;

 


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

 


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s