Skip to content Skip to main navigation Skip to footer

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.

NATURAL JOIN, JOIN ON and JOIN USING are types of inner joins.

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.”
LEFT OUTER Join

Column names in sample output have been abbreviated in order to fit on the page.

  • 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.
RIGHT OUTER Joins

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.
FULL OUTER Join Example

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);
Join Scenario

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *