 |
|
| You are reading part 4 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. |
|
|
|
|
Because an OUTER JOIN lets you see not only the matched rows but also the unmatched ones, it's great for finding out which, if any, rows in one table do not have a matching related row in another table. It also helps you find rows that have matches on a few rows but not on all. In addition, it's useful for creating input to a report where you want to show all categories (regardless of whether matching rows exist in other tables) or all customers (regardless of whether a customer has placed an order). Following is a small sample of the kinds of requests you can solve with an OUTER JOIN.
Find Missing Values
Sometimes you just want to find what's missing. You do so by using an OUTER JOIN with a test for Null. Here are some "missing value" problems you can solve.
"What products have never been ordered?"
"Show me customers who have never ordered a helmet."
"List entertainers who have never been booked."
"Display agents who haven't booked an entertainer."
"Show me tournaments that haven't been played yet."
"List the faculty members not teaching a class."
"Display students who have never withdrawn from a class."
"Show me classes that have no students enrolled."
"List ingredients not used in any recipe yet."
"Display missing types of recipes."
Find Partially Matched Information
Particularly for reports, it's useful to be able to list all the rows from one or more tables along with any matching rows from related tables. Here's a sample of "partially matched" problems you can solve with an OUTER JOIN.
"List all products and the dates for any orders."
"Display all customers and any orders for bicycles."
"Show me all entertainment styles and the customers who prefer those styles."
"List all entertainers and any engagements they have booked."
"List all bowlers and any games they bowled over 160."
"Display all tournaments and any matches that have been played."
"Show me all subject categories and any classes for all subjects."
"List all students and the classes for which they are currently enrolled."
"Display all faculty and the classes they are scheduled to teach."
"List all recipe types, all recipes, and any ingredients involved."
"Show me all ingredients and any recipes they're used in."
Part 1: 
