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