Manage Learn to apply best practices and optimize your operations.

SQL OUTER JOIN sample statements for queries

Discover sample SQL OUTER JOIN statements and result sets in this excerpt from "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Second Edition." Using sample databases and complex JOINs, these examples will help you master the process of solving SQL queries using OUTER JOINs.

By
Published: 01 Mar 2008

You now know the mechanics of constructing queries using OUTER JOIN and have seen some of the types of requests you can answer with an OUTER JOIN. Let's look at a fairly robust set of samples, all of which use OUTER JOIN. These examples come from each of the sample databases, and they illustrate the use of the OUTER JOIN to find either missing values or partially matched values.

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.

We've also included sample result sets that would be returned by these operations and placed them immediately after the SQL syntax line. The name that appears immediately above a result set is the name we gave each query in the sample data on the companion CD you'll find bound into the back of the book. We stored each query in the appropriate sample database (as indicated within the example) and prefixed the names of the queries relevant to this chapter with "CH09." You can follow the instructions in the Introduction of this book to load the samples onto your computer and try them.

Note: Because many of these examples use complex JOINs, the optimizer for your database system might choose a different way to solve these queries. For this reason, the first few rows might not exactly match the result you obtain, but the total number of rows should be the same. To simplify the process, we have combined the Translation and Clean Up steps for all the following examples.

Sales Orders Database

"What products have never been ordered?"

Translation/Clean Up Select product number and product name from the products table left outer joined with the order details table on products.product number in the products table matches = order_details .product number in the order details table where the order detail order number is null
SQL SELECT Products.ProductNumber, 
Products.ProductName
FROM Products LEFT OUTER JOIN Order_Details
ON Products.ProductNumber = 
Order_Details.ProductNumber
WHERE Order_Details.OrderNumber IS NULL

CH09_Products_Never_Ordered (2 rows)

Product Number Product Name
4 Victoria Pro All Weather Tires
23 Ultra-Pro Rain Jacket

"Display all customers and any orders for bicycles."

Translation 1 Select customer full name, order date, product name, quantity ordered, and quoted price from the customers table left outer joined with the orders table on customer ID, then joined with the order details table on order number, then joined with the products table on product number, then finally joined with the categories table on category ID where category description is 'Bikes'
Translation 2/Clean Up Select customer full name, order date, product name, quantity
ordered, and quoted price
from the customers table left outer joined with
(Select customer ID, order date, product name,
quantity ordered,and quoted price
from the orders table
inner joined with the order details table
on orders .order number in the orders table matches
= order_details .order number in the order details table,
then joined with the products table
on order_details.product number in the order details table
matches = products.product number in the products table,
then finally joined with the categories table
on categories.category ID in the categories table matches
= products.category ID in the products table
where category description is = 'Bikes') as rd
on customers.customer ID in the customers table matches
= rd.customerID in the embedded SELECT statement
Note: Because we're looking for specific orders (bicycles), we split the translation process into two steps to show that the orders need to be filtered before applying an OUTER JOIN.

 

SQL SELECT Customers.CustFirstName || ' ' ||
Customers.CustLastName AS CustFullName,
RD.OrderDate, RD.ProductName,
RD.QuantityOrdered, RD.QuotedPrice
FROM Customers
LEFT OUTER JOIN
(SELECT Orders.CustomerID, Orders.OrderDate,
Products.ProductName,
Order_Details.QuantityOrdered,
Order_Details.QuotedPrice
FROM ((Orders
INNER JOIN Order_Details
ON Orders.OrderNumber =
Order_Details.OrderNumber)
INNER JOIN Products
ON Order_Details.ProductNumber
= Products.ProductNumber)
INNER JOIN Categories
ON Categories.CategoryID =
Products.CategoryID
WHERE Categories.CategoryDescription =
'Bikes')
AS RD
ON Customers.CustomerID = RD.CustomerID

Note: This request is really tricky because you want to list all customers OUTER JOINed with only the orders for bikes. If you turn Translation 1 directly into SQL, you won't find any of the customers who have not ordered a bike! An OUTER JOIN from Customers to Orders will return all customers and any orders. When you add the filter to select only bike orders, that's all you will get—customers who ordered bikes.

Translation 2 shows you how to do it correctly—create an inner result set that returns only orders for bikes, and then OUTER JOIN that with Customers to get the final answer.

CH09_All_Customers_And_Any_Bike_Orders (913 rows)

CustFullName OrderDate ProductName QuantityOrdered QuotedPrice
Suzanne Viescas        
William Thompson 2007-12-23 Trek 9000
Mountain Bike		 5 $1,164.00
William Thompson 2008-01-15 Trek 9000
Mountain Bike		 6 $1,164.00
William Thompson 2007-10-11 Viscount
Mountain Bike		 2 $635.00
William Thompson 2007-10-05 Viscount
Mountain Bike		 5 $615.95
William Thompson 2008-01-15 Trek 9000
Mountain Bike		 4 $1,200.00
William Thompson 2007-10-11 Trek 9000
Mountain Bike		 3 $1,200.00
William Thompson 2008-01-07 Trek 9000
Mountain Bike		 2 $1,200.00

(Looks like William Thompson is a really good customer!)

Entertainment Agency Database

"List entertainers who have never been booked."

Translation/Clean Up Select entertainer ID and entertainer stage name
from the entertainers table
left outer join ed with the engagements table
on entertainers.entertainer ID in the entertainers table matches
= engagements.entertainer ID in the engagements table
where engagement number is null
SQL SELECT Entertainers.EntertainerID,
Entertainers.EntStageName
FROM Entertainers
LEFT OUTER JOIN Engagements
ON Entertainers.EntertainerID =
Engagements.EntertainerID
WHERE Engagements.EngagementNumber IS NULL

CH09_Entertainers_Never_Booked (1 row)

EntertainerID EntStageName
1009 Katherine Ehrlich

"Show me all musical styles and the customers who prefer those styles."

Translation/Clean Up Select style ID, style name, customer ID, customer first name,
and customer last name
from the musical styles table
left outer join ed with
( the musical preferences table inner join ed
with the customers table
on musical_preferences.customer ID
in the musical preferences table matches
= customers.customer ID in the customers table )
on musical_styles.style ID in the musical styles table matches
= musical_preferences.style ID in the musical preferences table
SQL SELECT Musical_Styles.StyleID,
Musical_Styles.StyleName,
Customers.CustomerID,
Customers.CustFirstName,
Customers.CustLastName
FROM Musical_Styles
LEFT OUTER JOIN (Musical_Preferences
INNER JOIN Customers
ON Musical_Preferences.CustomerID =
Customers.CustomerID)
ON Musical_Styles.StyleID =
Musical_Preferences.StyleID

CH09_All_Styles_And_Any_Customers (41 rows)

StyleID StyleName CustomerID CustFirstName CustLastName
1 40s Ballroom Music 10015 Carol Viescas
1 40s Ballroom Music 10011 Joyce Bonnicksen
2 50s Music      
3 60s Music 10002 Deb Waldal
4 70s Music 10007 Liz Keyser
5 80s Music 10014 Mark Rosales
6 Country 10009 Sarah Thompson
7 Classical 10005 Elizabeth Hallmark
< <more rows here> >        

(Looks like nobody likes 50s music!)

Note: We very carefully phrased the FROM clause to influence the database system to first perform the INNER JOIN between Musical_Preferences and Customers, and then OUTER JOINed that with Musical_Styles. If your database tends to process JOINs from left to right, you might have to state the FROM clause with the INNER JOIN first followed by a RIGHT OUTER JOIN to Musical_Styles. In Microsoft Office Access, we had to state the INNER JOIN as an embedded SELECT statement to get it to return the correct answer.

School Scheduling Database

"List the faculty members not teaching a class."

Translation/Clean Up Select staff first name and staff last name
from the staff table left outer join ed with the faculty classes table
on staff.staff ID in the staff table matches
= faculty_classes.staff ID in the faculty classes table
where class ID is null
SQL SELECT Staff.StfFirstName, Staff.StfLastName,
FROM Staff
LEFT OUTER JOIN Faculty_Classes
ON Staff.StaffID = Faculty_Classes.StaffID
WHERE Faculty_Classes.ClassID IS NULL

CH09_Staff_Not_Teaching (4 rows)

StfFirstName StfLastName
Jeffrey Smith
Tim Smith
Kathyrn Patterson
Joe Rosales III

"Display students who have never withdrawn from a class."

Translation/Clean Up Select student full name
from the students table left outer join ed with
(Select student ID from the student schedules table
inner join ed with the student class status table
on student_class_status.class status
in the student class status table matches
= student_schedules.class status in the student schedules table
where class status description is = 'withdrew') as withdrew
on students.student ID in the students table matches
= withdrew.student ID in the embedded SELECT statement
where the student_schedules.student ID in the
student schedules table is null
SQL SELECT Students.StudLastName || ', ' ||
Students.StudFirstName AS StudFullName
FROM Students
LEFT OUTER JOIN
(SELECT Student_Schedules.StudentID
FROM Student_Class_Status
INNER JOIN Student_Schedules
ON Student_Class_Status.ClassStatus =
Student_Schedules.ClassStatus
WHERE Student_Class_Status.ClassStatus
Description = 'withdrew')
AS Withdrew
ON Students.StudentID = Withdrew.StudentID
WHERE Withdrew.StudentID IS NULL

CH09_Students_Never_Withdrawn (15 rows)

StudFullName
Hamilton, David
Stadick, Betsy
Galvin, Janice
Hartwig, Doris
Bishop, Scott
Hallmark, Elizabeth
Sheskey, Sara
Wier, Marianne
< <more rows here> >

"Show me all subject categories and any classes for all subjects."

Translation/Clean Up Select category description, subject name, classroom ID,
start time, and duration
from the categories table
left outer join ed with the subjects table
on categories.category ID in the categories table matches
= subjects.category ID in the subjects table,
then left outer join ed with the classes table
on subjects .subject ID in the subjects table matches
= classes.subject ID in the classes table
SQL SELECT Categories.CategoryDescription,
Subjects.SubjectName, Classes.ClassroomID,
Classes.StartTime, Classes.Duration
FROM (Categories
LEFT OUTER JOIN Subjects
ON Categories.CategoryID = Subjects.CategoryID)
LEFT OUTER JOIN Classes
ON Subjects.SubjectID = Classes.SubjectID
Note: We were very careful again to construct the sequence and nesting of JOINs to be sure we got the answer we expected.

CH09_All_Categories_All_Subjects_Any_Classes (82 rows)

CategoryDescription SubjectName ClassroomID StartTime Duration
Accounting Financial
Accounting
Fundamentals I		 3313 9:00 50
Accounting Financial
Accounting
Fundamentals I		 3313 13:00 50
Accounting Financial
Accounting
Fundamentals II		 3415 8:00 50
Accounting Fundamentals
of Managerial
Accounting		 3415 10:00 50
Accounting Intermediate
Accounting		 3315 11:00 50
Accounting Business Tax
Accounting		 3313 14:00 50
Art Introduction
to Art		 1231 10:00 50
Art Design 1619 15:30 110
< <more rows here> >        

Further down in the result set, you'll find no classes scheduled for Developing a Feasibility Plan, Computer Programming, and American Government. You'll also find no subjects scheduled for categories Psychology, French, or German.

Bowling League Database

"Show me tournaments that haven't been played yet."

Translation/Clean Up Select tourney ID, tourney date, and tourney location
from the tournaments table
left outer join ed with the tourney matches table
on tournaments.tourney ID in the tournaments table matches
= tourney_matches.tourney ID in the tourney matches table
where match ID is null
SQL SELECT Tournaments.TourneyID,
Tournaments.TourneyDate,
Tournaments.TourneyLocation
FROM Tournaments
LEFT OUTER JOIN Tourney_Matches
ON Tournaments.TourneyID =
Tourney_Matches.TourneyID
WHERE Tourney_Matches.MatchID IS NULL

CH09_Tourney_Not_Yet_Played (6 rows)

TourneyID TourneyDate TourneyLocation
15 2008-07-11 Red Rooster Lanes
16 2008-07-18 Thunderbird Lanes
17 2008-07-25 Bolero Lanes
18 2008-08-01 Sports World Lanes
19 2008-08-08 Imperial Lanes
20 2008-08-15 Totem Lanes

"List all bowlers and any games they bowled over 180."

Translation 1 Select bowler name, tourney date, tourney location, match ID, and raw score from the bowlers table left outer joined with the bowler scores table on bowler ID, then inner joined with the tourney matches table on match ID, then finally inner joined with the tournaments table on tournament ID where raw score in the bowler scores table is greater than 180

Can you see why the above translation won't work? You need a filter on one of the tables that is on the right side of the left join, so you need to put the filter in an embedded SELECT statement. Let's restate the Translation step, clean it up, and solve the problem.

Translation 2/Clean Up Select bowler name, tourney date, tourney location,
match ID, and raw score
from the bowlers table left outer join ed with
(Select tourney date, tourney location, match ID,
bowler ID, and raw score
from the bowler scores table
inner join ed with the tourney matches table
on bowler_scores .match ID in the bowler scores table matches
= tourney_ matches.match ID in the tourney matches table,
then inner join ed with the tournaments table
on tournaments.tournament ID in the tournaments table matches
= tourney_ matches.tournament ID in the tourney matches table
where raw score is greater than > 180) as ti
on bowlers.bowler ID in the bowlers table matches
= ti.bowler ID in the embedded SELECT statement
SQL SELECT Bowlers.BowlerLastName || ', ' ||
Bowlers.BowlerFirstName AS BowlerName,
TI.TourneyDate, TI.TourneyLocation,
TI.MatchID, TI.RawScore
FROM Bowlers
LEFT OUTER JOIN
(SELECT Tournaments.TourneyDate,
Tournaments.TourneyLocation,
Bowler_Scores.MatchID,
Bowler_Scores.BowlerID,
Bowler_Scores.RawScore
FROM (Bowler_Scores
INNER JOIN Tourney_Matches
ON Bowler_Scores.MatchID =
Tourney_Matches.MatchID)
INNER JOIN Tournaments
ON Tournaments.TourneyID =
Tourney_Matches.TourneyID
WHERE Bowler_Scores.RawScore > 180)
AS TI
ON Bowlers.BowlerID = TI.BowlerID

CH09_All_Bowlers_And_Scores_Over_180 (106 rows)

BowlerName TourneyDate TourneyLocation MatchID RawScore
Black, Alastair        
Cunningham, David        
Ehrlich, Zachary        
Fournier, Barbara        
Fournier, David        
Hallmark, Alaina        
Hallmark, Bailey        
Hallmark, Elizabeth        
Hallmark, Gary        
Hernandez, Kendra        
Hernandez, Michael        
Kennedy, Angel 2007-11-20 Sports World Lanes 46 185
Kennedy, Angel 2007-10-09 Totem Lanes 22 182
< <more rows here> >        
Note:You guessed it! This is another example where you must build the filtered INNER JOIN result set first and then OUTER JOIN that with the table from which you want "all" row.

Recipes Database

"List ingredients not used in any recipe yet."

Translation/Clean Up Select ingredient name from the ingredients table
left outer join ed with the recipe ingredients table
on ingredients.ingredient ID in the ingredients table matches
= recipe_ingredients.ingredient ID in the recipe ingredients table
where recipe ID is null
SQL SELECT Ingredients.IngredientName
FROM Ingredients
LEFT OUTER JOIN Recipe_Ingredients
ON Ingredients.IngredientID =
Recipe_Ingredients.IngredientID
WHERE Recipe_Ingredients.RecipeID IS NULL

CH09_Ingredients_Not_Used (20 rows)

IngredientName
Halibut
Chicken, Fryer
Bacon
Iceberg Lettuce
Butterhead Lettuce
Scallop
Vinegar
Red Wine
< <more rows here> >

"I need all the recipe types, and then all the recipe names, and then any matching ingredient step numbers, ingredient quantities, and ingredient measurements, and finally all ingredient names from my recipes database."

Translation/Clean Up Select the recipe class description, recipe title,
ingredient name, recipe sequence number,
amount, and measurement description
from the recipe classes table
full outer join ed 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 join ed 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 join ed 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 join ed with the ingredients table
on ingredients.ingredient ID in the ingredients table matches
= recipe_ ingredients.ingredient ID in the recipe ingredients table,
SQL SELECT Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle,
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
ON Recipe_Classes.RecipeClassID =
Recipes.RecipeClassID
Note: This sample is a request you saw us solve in the section on FULL OUTER JOIN. We decided to include it here so that you can see the actual result. You won't find this query saved using this syntax in the Microsoft Access or MySQL version of the sample database because neither product supports a FULL OUTER JOIN. Instead, you can find this problem solved with a UNION of two OUTER JOIN queries that achieves the same result. You'll learn about using UNION in the next chapter. The result shown here is what you'll see when you run the query in Microsoft SQL server.

CH09_All_Recipe_Classes_All_Recipes (109 rows)

RecipeClass
Description		 RecipeTitle Ingredient
Name		 RecipeSeqNo Amount Measurement
Description
Starch Yorkshire
Pudding		 Flour 1 1.5 Cup
Starch Yorkshire
Pudding		 Water 2 1 Cup
Starch Yorkshire
Pudding		 Eggs 3 2 Piece
Starch Yorkshire
Pudding		 Salt 4 0.5 Teaspoon
Starch Yorkshire
Pudding		 Milk 5 0.5 Cup
Starch Yorkshire
Pudding		 Beef drippings 6 4 Teaspoon
Dessert Trifle Sponge
Cake		 1 1 Package
Dessert Trifle Raspberry
Jello		 2 1 Package
Dessert Trifle Bird's Custard
Powder		 3 1 Package
Dessert Trifle Raspberry
Jam		 4 1 Jar
< <more rows here> >          

The following section presents a number of requests that you can work out on your own.

Problems for You to Solve

Below, we show you the request statement and the name of the solution query in the sample databases. If you want some practice, you can work out the SQL you need for each request and then check your answer with the query we saved in the samples. Don't worry if your syntax doesn't exactly match the syntax of the queries we saved -- as long as your result set is the same.

Sales Orders Database

  1. "Show me customers who have never ordered a helmet."
    (Hint: This is another request where you must first build an INNER JOIN to find all orders containing helmets and then do an OUTER JOIN with Customers.) You can find the solution in CH09_Customers_No_Helmets (2 rows).
  2. "Display customers who have no sales rep (employees) in the same Zip code."
    You can find the solution in CH09_Customers_No_Rep_Same_Zip (18 rows).
  3. "List all products and the dates for any orders."
    You can find the solution in CH09_All_Products_Any_Order_Dates (2,682 rows).

Entertainment Agency Database

  1. "Display agents who haven't booked an entertainer."
    You can find the solution in Agents_No_Contracts (1 row).
  2. "List customers with no bookings."
    You can find the solution in CH09_Customers_No_Bookings (2 rows).
  3. "List all entertainers and any engagements they have booked."
    You can find the solution in CH09_All_Entertainers_And_Any_Engagements (112 rows).

School Scheduling Database

  1. "Show me classes that have no students enrolled."
    (Hint: You need only "enrolled" rows from Student_Classes, not "completed" or "withdrew.") You can find the solution in CH09_Classes_No_Students_Enrolled (63 rows).
  2. "Display subjects with no faculty assigned."
    You can find the solution in CH09_Subjects_No_Faculty (1 row).
  3. "List students not currently enrolled in any classes."
    (Hint: You need to find which students have an "enrolled" class status in student schedules and then find the students who are not in this set.) You can find the solution in CH09_Students_Not_Currently_Enrolled (2 rows).
  4. "Display all faculty and the classes they are scheduled to teach."
    You can find the solution in CH09_All_Faculty_And_Any_Classes (79 rows).

Bowling League Database

  1. "Display matches with no game data."
    You can find the solution in CH09_Matches_Not_Played_Yet (1 row).
  2. "Display all tournaments and any matches that have been played."
    You can find the solution in CH09_All_Tourneys_Match_Results (174 rows).

Recipes Database

  1. "Display missing types of recipes."
    You can find the solution in CH09_Recipe_Classes_No_Recipes (1 row).
  2. "Show me all ingredients and any recipes they're used in."
    You can find the solution in CH09_All_Ingredients_Any_Recipes (108 rows).
  3. "List the salad, soup, and main course categories and any recipes."
    You can find the solution in CH09_Salad_Soup_Main_Courses (9 rows).
  4. "Display all recipe classes and any recipes."
    You can find the solution in CH09_All_RecipesClasses_And_Matching_Recipes (16 rows).

Summary

In this chapter, we led you through the world of OUTER JOINs. We began by defining an OUTER JOIN and comparing it to the INNER JOIN you learned about in Chapter 8.

More on SQL Server performance

Check out these guidelines for database index design and optimization

Learn to streamline data SQL Server database peformance

Read about identifying and fixing bad SQL query performance

We next explained how to construct a LEFT or RIGHT OUTER JOIN, beginning with simple examples using two tables, and then progressing to embedding SELECT statements and constructing statements using multiple JOINs. We showed how an OUTER JOIN combined with a Null test is equivalent to the difference (EXCEPT) operation we covered in Chapter 7. We also discussed some of the difficulties you might encounter when constructing statements using multiple OUTER JOINs. We closed the discussion of the LEFT and RIGHT OUTER JOIN with a problem requiring multiple OUTER JOINs that can't be solved with only LEFT or RIGHT.

In our discussion of FULL OUTER JOIN, we showed how you might need to use this type of JOIN in combination with other INNER and OUTER JOINs to get the correct answer. We also briefly explained a variant of the FULL OUTER JOIN – the UNION JOIN.

We explained how OUTER JOINs are useful and listed a variety of requests that you can solve using OUTER JOINs. The rest of the chapter showed nearly a dozen examples of how to use OUTER JOIN> We provided several examples for each of the sample databases and showed you the logic behind constructing the solution statement for each request.

TABLE OF CONTENTS

Exchange 2007 Storage Systems 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.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close