Manage Learn to apply best practices and optimize your operations.

SQL OUTER JOIN sample uses

Find out how OUTER JOIN is used in this excerpt from "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Second Edition." You'll get samples of SQL requests solved with OUTER JOIN, such as testing for Null.

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.

You Are Now 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

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."


TABLE OF CONTENTS

This was last published in March 2008

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Have used full outer joins when attempting to find mismatched, orphaned data, from both of my tables and wanted all of my result set, not just matches. For reports which require data from multiple tables, full outer joins are one of the useful options.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close