JOINS REVISITED

JOINS REVISITED

            In SQL, JOIN Clause combines records from two or more tables in database. There are different types of Joins and let’s discuss them one by one.

            Consider the example of an office which has employees and different departments.

Table Employee:

Emp_ID	Name	Mnger_ID	Dept_ID
1001	Ajay	1005	10
1002	Bipin	1005	10
1003	Chandra	1005	20
1004	Mike		30
1005	Raj	1004	30
1006	Sandy	1005	50

Table Department:

Dept_ID	Dept_Name
10	Marketing
20	Sales
30	Management
40	Finance

INNER JOIN:  Inner Join is JOINING between two tables on a KEY. In above scenario it is used to list down the Department Name for each Employee based on KEY Dept_ID. It will show only matched results.

The results for above scenarios will be as follows.  This is most common type of JOIN used in real time scenarios.

Emp_ID	Name	Mnger_ID	Dept_ID	Dept_Name
1001	Ajay	1005	10	Marketing
1002	Bipin	1005	10	Marketing
1003	Chandra	1005	20	Sales
1004	Mike		30	Management
1005	Raj	1004	30	Management

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.

It is further classified as

LEFT OUTER JOIN:  Left Outer JOIN between Table Employee (Left) and Department (Right) will list down all the records from Employee Table with matched Departments.

The results for above scenarios will be as follows.  You can notice here that we can see Employee Name “Sandy” though it doesn’t has any valid Department.

Emp_ID	Name	Mnger_ID	Dept_ID	Dept_Name
1001	Ajay	1005	10	Marketing
1002	Bipin	1005	10	Marketing
1003	Chandra	1005	20	Sales
1004	Mike		30	Management
1005	Raj	1004	30	Management
1006	Sandy	1005	50	<NULL>

RIGHT OUTER JOIN:  Right Outer JOIN between Table Employee (Left) and Department (Right) will list down matched records from Employee Table with all Departments.

The results for above scenarios will be as follows.  You can notice here that we can see Department Name “Finance” though it doesn’t has any Employee. In practical, this type of JOIN is rarely used.

Emp_ID	Name	Mnger_ID	Dept_ID	Dept_Name
1001	Ajay	1005	10	Marketing
1002	Bipin	1005	10	Marketing
1003	Chandra	1005	20	Sales
1004	Mike		30	Management
1005	Raj	1004	30	Management
<NULL>	<NULL>	<NULL>	40	Finance

FULL OUTER JOIN: A Full Outer Join combines the effect of applying both left and right outer joins i.e. all the records from both tables are matched and not matched records are displayed as NULL.

The results for above scenarios will be as follows.

Emp_ID	Name	Mnger_ID	Dept_ID	Dept_Name
1001	Ajay	1005	10	Marketing
1002	Bipin	1005	10	Marketing
1003	Chandra	1005	20	Sales
1004	Mike		30	Management
1005	Raj	1004	30	Management
<NULL>	<NULL>	<NULL>	40	Finance
1006	Sandy	1005	50	<NULL>

SELF JOIN: Self Join is joining a table to itself. In the above scenarios Self Join is used to determine the Manager Name for each Employee.

The results for above scenarios will be as follows. As Mike doesn’t have any Manager, we can’t fine the row for him in the results.

Emp_ID	Name	Mnger Name
1001	Ajay	Raj
1002	Bipin	Raj
1003	Chandra	Raj
1005	Raj	Mike