What are SQL JOINS? Difference between, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, SELF JOIN?

Joins are the ways to combine one or more tables (or views, or synonyms). Tables are joined two at a time making a new table containing all possible combinations of rows from the original two tables.

It is a little difficult to explain the difference between all the types of JOIN. I have tried to explain in terms of VENN DIAGRAMS in the above figure.

Employees table
Id firstname Surname state
1001 John Darcy NSW
1002 Peter Smith NSW
1003 Paul Gregor NSW
1004 Sam Darcy VIC

Executives table
Id firstname Surname state
1001 John Darcy NSW
1002 Peter Smith NSW
1005 John Gregor WA

To see how the different JOINS work on the above tables read more

Inner joins: Chooses the join criteria using any column names that happen to match between the two tables. The example below displays only the employees who are executives as well.
Query: SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id = exec.id;
The output is:
Firstname surname
John Darcy
Peter Smith

Left Outer joins: This gives all the rows that match between the two tables plus all the entries in left table and corresponding null value for the right table. The example below will show all the employees and fill the null data for the executives.
Query: SELECT emp.firstname, exec.surname FROM employees emp left join executives exec ON emp.id = exec.id;
On oracle: SELECT emp.firstname, exec.surname FROM employees emp, executives exec where emp.id = exec.id(+);
The output is:
Firstname surname
John Darcy
Peter Smith
Paul null
Sam null

Right Outer join: This gives all the rows that match between the two tables plus all the entries in right table and corresponding null value for the left table. The example below will show all the executives and fill the null data for the employees.
Query: SELECT emp.firstname, exec.surname FROM employees emp right join executives exec ON emp.id = exec.id;
On oracle: SELECT emp.firstname, exec.surname FROM employees emp, executives exec WHERE emp.id(+) = exec.id;
The output is:
Firstname surname
John Darcy
Peter Smith
Gregor

Full outer join: Left outer join + right outer join
Query: SELECT emp.firstname, exec.surname FROM employees emp full join executives exec ON emp.id = exec.id;
On oracle:
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id = exec.id (+)
UNION
SELECT emp.firstname, exec.surname FROM employees emp, executives exec
WHERE emp.id(+) = exec.id
The output is:
Firstname surname
John Darcy
Paul
Peter Smith
Sam
Gregor

Self join: A self-join is a join of a table to itself. If you want to find out all the employees who live in the same city as employees whose first name starts with “Peter”, then one way is to use a sub-query as shown below:
Query: SELECT emp.firstname, emp.surname FROM employees emp WHERE city IN (SELECT city FROM employees where firstname like ‘Peter’)
The sub-queries can degrade performance. So alternatively we can use a self-join to achieve the same results.
On oracle:
SELECT emp.firstname, emp.surname FROM employees emp, employees emp2
WHERE emp.state = emp2.state
AND emp2.firstname LIKE ‘Peter’

The output is:
Firstname Surname
John Darcy
Peter Smith
Paul Gregor

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