Inner versus Outer Joins
Objectives
This tutorial covers the following objectives:
- Compare and contrast an inner and an outer join
- Construct and execute a query to use a left outer join
- Construct and execute a query to use a right outer join
- Construct and execute a query to use a full outer join
Purpose
- Up to now, all of the joins returned data that matched the join condition.
- Sometimes, however, we want to retrieve both the data that meets the join condition, and the data that does not meet the join condition.
- The outer joins in ANSI-99 SQL allow this functionality.
INNER And OUTER Joins
- In ANSI-99 SQL, a join of two or more tables that returns only the matched rows is called an inner join.
- When a join returns the unmatched rows as well as the matched rows, it is called an outer join.
- Outer join syntax uses the terms “left, full, and right”.
- These names are associated with the order of the table names in the FROM clause of the SELECT statement.
LEFT and RIGHT OUTER Joins
- In the example shown of a left outer join, note that the table name listed to the left of the words “left outer join” is referred to as the “left table.”

- The above query will return all employee last names, both those that are assigned to a department and those that are not
- The following right outer join would return all department IDs and department names, both those that have employees assigned to them and those that do not.

FULL OUTER Join
- It is possible to create a join condition to retrieve all matching rows and all unmatched rows from both tables.
- Using a full outer join solves this problem.
- The result set of a full outer join includes all rows from a left outer join and all rows from a right outer join combined together without duplication.
FULL OUTER Join Example
- The example shown is a full outer join.

Join Scenario
- Construct a join to display a list of employees, their current job_id and any previous jobs they may have held.
- The job_history table contains details of an employee’s previous jobs.
SELECT last_name, e.job_id AS "Job", jh.job_id AS "Old job", end_date
FROM employees e LEFT OUTER JOIN job_history jh
ON(e.employee_id = jh.employee_id);

Terminology
Key terms used in this tutorial included:
- FULL OUTER JOIN
- Inner join
- LEFT OUTER JOIN
- Outer join
- RIGHT OUTER JOIN
Summary
In this tutorial, you should have learned how to:
- Compare and contrast an inner and an outer join
- Construct and execute a query to use a left outer join
- Construct and execute a query to use a right outer join
- Construct and execute a query to use a full outer join
0 Comments