Live Crab Vending Machine In The Subway Station

Vending machine sells live crabs.

"Honey, please pick up a live crab on your way home from the subway station vending machine."

SQL Outer Join Query

Outer Join Query

SQL OUTER JOIN returns all records from both joined tables even there is no matching record found. There are three types of SQL OUTER JOIN:

• LEFT OUTER JOIN
• RIGHT OUTER,
• FULL OUTER JOIN

In Outer Join, preserved table means that all the rows in the rows in preserved table will be returned from the query results with at least one row. Let's use the following two tables to show the different type of outer joins.

Employee table: employee id and name

Emp_id	Name
100	John
110	Mary
111	Smith
112	Bob
113	Jim
114	Joan
115	Jill

Employee_hours_worked table: daily total hours each employee worked.

Emp_id	Date	Total_hours_worked
1	2009-12-04	1
100	2010-01-12	3
110	2010-01-12	5
113	2010-01-13	2
111	2010-01-13	4
113	2010-01-13	6
110	2010-01-13	2

1. Left Outer Join

Left outer join preserve the table in the left side. Every row in the preserved table is presented in the result set with at least one result row.

select e.emp_id,e.name, w.date,w.total_hours_worked
from employee e
left join employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worked
100	John	1/12/2010	3
110	Mary	1/12/2010	5
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	bob
113	Jim	1/12/2010	2
113	Jim	1/13/2010	6
114	Joan
114	Jill

Put search predicate (filter) in the WHERE clause in a left outer join query will filter out the result set.

select e.emp_id,e.name, w.date,w.total_hours_worked
from employee e
left join employee_hours_worked w
on e.emp_id = w.emp_id
where w.total_hours_worked <=4

emp_id	name	date	total_hours_worked
110	Joan	1/12/2010	3
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
113	Jim	1/12/2010	2

Put search predicate in the ON clause in a left outer join query will filter out only the matched records in the non preserved table.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
left join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id and w.total_hours_worked <=4
emp_id name date total_hours_worked

emp_id	name	date	total_hours_worked
100	john	1/12/2010	3
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	Bob
113	Jim	1/12/2010	2
114	Joan
114	Jill

2 Right Outer Join

In Right outer join, the preserved table is on the right with is opposite to Left outer Join.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
right outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worked
100	John	1/12/2010	3
110	Mary	1/12/2010	5
113	Jim	1/12/2010	2
111	Smith	1/13/2010	4
113	Jim	1/13/2010	2
110	Mary	1/13/2010	2
		12/4/2009	1

Notice that the result set include the record that has not emp_id and name (last record).

3. Full Outer Join

Full Outer Join preserved both tables

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
full outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id

emp_id	name	date	total_hours_worded
100	John	1/12/2010	3
110	Mary	1/12/2010	5
110	Mary	1/13/2010	2
111	Smith	1/13/2010	4
112	Bob
113	Jim	1/12/2010	2
113	Jim	1/13/2010	6
114	Joan
115	Jim
		12/4/2009	1

If a database server (like MySQL) dose not support full outer join, the query can be written with an union and a where clasue.

select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
left outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id
union
select e.emp_id,e.name, w.date,w.total_hours_worked
from pcdb.employee e
right outer join pcdb.employee_hours_worked w
on e.emp_id = w.emp_id
where e.emp_id is null

The where clause in the second query ,'where e.emp_id is null', keep all the the records that only exists in the right table, employee_hours_worked.

Reference

• SQL For Smarties by Joe Celko 
• MySQL Cookbook by Paul DuBois 
• Oracle Pl/SQL Programming by Steven Feuerstein and Bill Pribyl

Man Vs. Machine - The Final Jeopardy!

Man Vs. Machine  - The  Final Jeopardy!

This is amazing!



IBM Watson