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."
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.
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|
full outer join
on recipe_classes .recipe class ID
= recipes .recipe class ID
on recipes.recipe ID
= recipe_ingredients.recipe ID
on measurements.measurement amount ID
= recipe_ingredients.measurement amount ID
on ingredients.ingredient ID
= recipe_ingredients .ingredient ID
order by recipe title and recipe sequence number
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.)
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.
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.
"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
full outer join
on students.first name
= staff.first name
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.