Inner and Outer Joins!..

Join us on Facebook 

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.

Frequently asked SQL queries in interview..

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.

All about Normalization!

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