Using the FULL OUTER JOIN in SQL

 
You are reading part 3 from "How to construct and use SQL OUTER JOINs optimally," excerpted from SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition), by John L. Viescas and Michael J. Hernandez, copyright 2007, printed with permission from Addison-Wesley Professional.

A FULL OUTER JOIN is neither "left" nor "right"— it's both! It includes all the rows from both of the tables or result sets participating in the JOIN. When no matching rows exist for rows on the "left" side of the JOIN, you see Null values from the result set on the "right." Conversely, when no matching rows exist for rows on the "right" side of the JOIN, you see Null values from the result set on the "left."

Syntax

Now that you've been working with JOINs for a while, the syntax for a FULL OUTER JOIN should be pretty obvious. You can study the syntax diagram for a FULL OUTER JOIN in Figure 9–13.

    Requires Free Membership to View

    Login


Figure 9–13 The syntax diagram for a FULL OUTER JOIN

To simplify things, we're now using the term table reference in place of a table name, a SELECT statement, or the result of another JOIN. Let's take another look at the problem we introduced at the end of the previous section. We can now solve it properly using a FULL OUTER JOIN.

"I need all the recipe types, and then all the recipe names and preparation instructions, and then any matching ingredient step numbers, ingredient quantities, and ingredient measurements, and finally all ingredient names from my recipes database, sorted in recipe title and step number sequence."

 

 
Translation Select the recipe class description, recipe title, preparation instructions, ingredient name, recipe sequence number, amount, and measurement description from the recipe classes table full outer joined with the recipes table on recipe class ID in the recipe classes table matches recipe class ID in the recipes table, then left outer joined with the recipe ingredients table on recipe ID in the recipes table matches recipe ID in the recipe ingredients table, then joined with the measurements table on measurement amount ID in the measurements table matches measurement amount ID in the recipe ingredients table, and then finally full outer joined with the ingredients table on ingredient ID in the ingredients table matches ingredient ID in the recipe ingredients table, order by recipe title and recipe sequence number
Clean Up Select the recipe class description, recipe title, preparation
instructions, ingredient name, recipe sequence number,
amount, and measurement description
from the recipe classes table
full outer joined with the recipes table
on recipe_classes .recipe class ID in the recipe classes table matches
= recipes .recipe class ID in the recipes table,
then left outer joined with the recipe ingredients table
on recipes.recipe ID in the recipes table matches
= recipe_ingredients.recipe ID in the recipe ingredients table,
then inner joined with the measurements table
on measurements.measurement amount ID
in the measurements table matches
= recipe_ingredients.measurement amount ID
in the recipe ingredients table,
and then finally full outer joined with the ingredients table
on ingredients.ingredient ID in the ingredients table matches
= recipe_ingredients .ingredient ID in the recipe ingredients table,
order by recipe title and recipe sequence number
SQL SELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle, Recipes.Preparation,
Ingredients.IngredientName,
Recipe_Ingredients.RecipeSeqNo,
Recipe_Ingredients.Amount,
Measurements.MeasurementDescription
FROM (((Recipe_Classes
FULL OUTER JOIN Recipes
ON Recipe_Classes.RecipeClassID =
Recipes.RecipeClassID)
LEFT OUTER JOIN Recipe_Ingredients
ON Recipes.RecipeID =
Recipe_Ingredients.RecipeID)
INNER JOIN Measurements
ON Measurements.MeasureAmountID =
Recipe_Ingredients.MeasureAmountID)
FULL OUTER JOIN Ingredients
ON Ingredients.IngredientID =
Recipe_Ingredients.IngredientID
ORDER BY RecipeTitle, RecipeSeqNo

The first and last JOINs now ask for all rows from both sides of the JOIN, so the problem with Nulls not matching is solved. You should now see not only recipe classes for which there are no recipes and recipes for which there are no ingredients but also ingredients that haven't been used in a recipe yet. You might get away with using a LEFT OUTER JOIN for the first JOIN, but because you can't predict in advance how your database system decides to nest the JOINs, you should ask for a FULL OUTER JOIN on both ends to ensure the right answer.

 

Note: As you might expect, database systems that do not support the SQL Standard syntax for LEFT OUTER JOIN or RIGHT OUTER JOIN also have a special syntax for FULL OUTER JOIN. You must consult your database documentation to learn the specific nonstandard syntax that your database requires to define the OUTER JOIN. For example, earlier versions of Microsoft SQL Server support the following syntax. (Notice the asterisks in the WHERE clause.)

SELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle
FROM Recipe_Classes, Recipes
WHERE Recipe_Classes.RecipeClassID *=*
Recipes.RecipeClassID

Products that do not support any FULL OUTER JOIN syntax but do support LEFT or RIGHT OUTER JOIN yield an equivalent result by performing a UNION on a LEFT and RIGHT OUTER JOIN. We'll discuss UNION in more detail in the next chapter. Because the specific syntax for defining a FULL OUTER JOIN using the WHERE clause varies by product, you might have to learn several different syntaxes if you work with multiple nonstandard products.

FULL OUTER JOIN on Non-Key Values

Thus far, we have been discussing using OUTER JOINs to link tables or result sets on related key values. You can, however, solve some interesting problems by using an OUTER JOIN on non-key values. For example, the previous chapter showed how to find students and staff who have the same first name in the School Scheduling database. Suppose you're interested in listing all staff members and all students and showing the ones who have the same first name as well. You can do that with a FULL OUTER JOIN.

"Show me all the students and all the teachers and list together those who have the same first name."

 

 
Translation Select student full name and staff full name from the students table full outer joined with the staff table on first name in the students table matches first name in the staff table
Clean Up Select student full name and staff full name
from the students table
full outer joined with the staff table
on students.first name in the students table matches
= staff.first name in the staff table
SQL SELECT (Students.StudFirstName || ' ' ||
Students.StudLastName) AS StudFullName,
(Staff.StfFirstName || ' ' ||
Staff.StfLastName) AS StfFullName
FROM Students
FULL OUTER JOIN Staff
ON Students.StudFirstName =
Staff.StfFirstName

UNION JOIN

No discussion of OUTER JOINs would be complete without at least an honorable mention to UNION JOIN. In the SQL Standard, a UNION JOIN is a FULL OUTER JOIN with the matching rows removed. Figure 9–14 shows the syntax.


Figure 9–14 The SQL syntax for a UNION JOIN

As you might expect, not many commercial implementations support a UNION JOIN. Quite frankly, we're hard pressed to think of a good reason why you would want to do a UNION JOIN.

 

 

TABLE OF CONTENTS
   Part 1: How to construct and use SQL OUTER JOINs optimally
   Part 2: How to use the LEFT vs. the RIGHT OUTER JOIN
   Part 3: Using the FULL OUTER JOIN in SQL
   Part 4: SQL OUTER JOIN sample uses
   Part 5: SQL OUTER JOIN sample statements for queries

 

 

This chapter excerpt from  SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez, is printed with permission from Addison-Wesley Professional, Copyright 2007.

Click here for the chapter download or purchase the book here.

  

This was first published in March 2008

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top