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.
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.
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.
Sales Orders Database
"What products have never been ordered?"
Translation/Clean Up | Select product number |
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, from (Select customer ID, order date, product name, quantity ordered, from inner join on orders .order number = order_details .order number on order_details.product number on categories.category ID = products.category ID where category description on customers.customer ID in the customers table matches = rd.customerID |
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 from left outer join on entertainers.entertainer ID = engagements.entertainer ID 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, from left outer join ( on musical_preferences.customer ID = customers.customer ID on musical_styles.style ID = musical_preferences.style ID |
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!)
School Scheduling Database
"List the faculty members not teaching a class."
Translation/Clean Up | Select staff first name from on staff.staff ID = faculty_classes.staff ID 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 (Select student ID from inner join on student_class_status.class status = student_schedules.class status where class status description is = 'withdrew') as withdrew on students.student ID = withdrew.student ID where the student_schedules.student ID |
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, from left outer join on categories.category ID = subjects.category ID on subjects .subject ID = classes.subject ID |
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 |
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, from left outer join on tournaments.tourney ID = tourney_matches.tourney ID 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, from (Select tourney date, tourney location, match ID, bowler ID, from inner join on bowler_scores .match ID = tourney_ matches.match ID on tournaments.tournament ID = tourney_ matches.tournament ID where raw score on bowlers.bowler ID = ti.bowler ID |
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> > |
Recipes Database
"List ingredients not used in any recipe yet."
Translation/Clean Up | Select ingredient name from left outer join on ingredients.ingredient ID = recipe_ingredients.ingredient ID 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 ingredient name, recipe sequence number, amount, from 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 |
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 |
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
- "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). - "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). - "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
- "Display agents who haven't booked an entertainer."
You can find the solution in Agents_No_Contracts (1 row). - "List customers with no bookings."
You can find the solution in CH09_Customers_No_Bookings (2 rows). - "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
- "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). - "Display subjects with no faculty assigned."
You can find the solution in CH09_Subjects_No_Faculty (1 row). - "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). - "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
- "Display matches with no game data."
You can find the solution in CH09_Matches_Not_Played_Yet (1 row). - "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
- "Display missing types of recipes."
You can find the solution in CH09_Recipe_Classes_No_Recipes (1 row). - "Show me all ingredients and any recipes they're used in."
You can find the solution in CH09_All_Ingredients_Any_Recipes (108 rows). - "List the salad, soup, and main course categories and any recipes."
You can find the solution in CH09_Salad_Soup_Main_Courses (9 rows). - "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
- 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.