May 25, 2014

Inner and Outer Joins!..




JOINS are basically used to combine the data from two tables. The temporary table is created based on column(s) that the two tables share, which represent meaningful column(s) of comparison.

The main aim of Joins is to extract meaningful data from the resulting temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join.

Types of Joins:
             INNER JOIN: Returns all rows when there is at least one match in both tables
             LEFT JOIN: Return all rows from the left table, and the matched rows from the right table.
             RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.
             FULL OUTER JOIN: Return all rows when there is a match in one of the tables .

Lets take an example to illustrate the difference between two types of joins:



EMPLOYEE
EMP_ID
EMP_NAME
DEPT_ID
1
ram
d1
2
shyam
d4
3
raj
d2
4
rohan
d1
5
sohan
d5


DEPARTMENT
DEPT_ID
DEPT_NAME
d1
Sales
d2
Marketing
d3
HR
d4
Accounts

NOTE: Department details for employee 5 is not present in Department table. Also Dept_ID d5 is not present in Employee table.

Inner Join
This is the simplest and is the most common join. Below query will return all of the records in the left table (table Employee) that have a matching record in the right table (table Location). 
select * from employee inner join department on employee.dept_id = department.dept_id;
Left Join
Below query will return all of the records in the left table (table Employee) regardless if any of those records have a match in the right table (table Location). It will also return any matching records from the right table.
select * from employee left join department on employee.dept_id = department.dept_id; 
Right Join
Below query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table.

select * from employee right join department on employee.dept_id = department.dept_id;


Outer Join
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. Below query will return all of the records from both tables, joining records from the left table (table Employee) that match records from the right table (table Location).

select * from employee full outer join department on employee.dept_id = department.dept_id;


Inner vs Outer Joins
Inner join will only return rows in which there is a match based on the join predicate. In above case, what that means is anytime the Employee and Department table share an Dept_D, a row will be generated in the results to show the match.

But, in the  left or right outer join, the result set will retain all of the rows from either the left or right table.

-K Himaanshu Shukla...



Copyright © 2014 - ScrutinyByKHimaanshu

No comments:

Post a Comment

RSSChomp Blog Directory