DbMS: Joins

Joins are used to merge data based on predicate logic, of two tables that results in a temporary table;

Types:

  1. Outer Joins
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join
  2. Inner Join

Consider tables for examples:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India

Outer Join: Iterates over the all rows of both or one of the tables.

In left outer join, according to the query, it iterates over the key of left table provided in SQL query and matches the same in the right one. Tables will result in:

SQL:
select * from employee left outer join location on employee.empID = location.empID; OR
select * from employee left join location on employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL

In right outer join, same happens for the right table:

SQL:
select * from employee right outer join location on employee.empID = location.empID; OR
select * from employee right join location on employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
NULL NULL 39 Bangalore, India

Inner Join: Here if match happens only then join happens.

SQL:
select * from employee inner join location on employee.empID = location.empID; OR
select * from employee, location where employee.empID = location.empID;

Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India

 

Souirce: http://www.programmerinterview.com

Advertisements

Help to improve or comment as you wish

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