Learn the basics of a SQL Server OUTER JOIN in this excerpt from "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Second Edition." You'll discover how to construct LEFT, RIGHT and FULL OUTER JOINs with how-to examples through sample statements and tables. Authors John L. Viescas and Michael J. Hernandez also examine problems that may occur when using multiple OUTER JOINs and how to solve them.
What is an OUTER JOIN?
 |
|
| If you have any comments or questions about the information presented in this book excerpt, please send an email to editor@searchsqlserver.com. |
|
|
|
|
As we explained in the previous chapter, the SQL Standard defines several types of JOIN operations to link two or more tables or result sets. An OUTER JOIN asks your database system to return not only the rows that match on the criteria you specify but also the unmatched rows from either one or both of the two sets you want to link.
Let's suppose, for example, that you want to fetch information from the School Scheduling database about students and the classes for which they're registered. As you learned in the previous chapter, an INNER JOIN returns only students who have registered for a class and classes for which a student has registered. It won't return any students who have been accepted at the school but haven't signed up for any classes yet, nor will it return any classes that are on the schedule but for which no student has yet shown an interest.
What if you want to list all students and the classes for which they are registered, if any? Conversely, suppose you want a list of all the classes and the students who have registered for those classes, if any. To solve this sort of problem, you need to ask for an OUTER JOIN.
Figure 9–1 uses a set diagram to show one possible relationship between students and classes. As you can see, a few students haven't registered for a class yet, and a few classes do not yet have any students signed up to take the class.
Figure 9–1 A possible relationship between students and classes
If you ask for all students and the classes for which they are registered, you'll get a result set resembling Figure 9–2.
Figure 9–2 All students and the classes for which they are registered
You might ask, "What will I see for the students who haven't registered for any classes?" If you remember the concept of a Null or "nothing" value discussed in Chapter 5, Getting More Than Simple Columns, you know what you'll see: When you ask for all students joined with any classes, your database system will return a Null value in all columns from the Classes table when it finds a student who is not yet registered for any classes. If you think about the concept of a difference between two sets (discussed in Chapter 7, Thinking in Sets), the rows with a Null value in the columns from the Classes table represent the difference between the set of all students and the set of students who have registered for a class.
Likewise, if you ask for all classes and any students who registered for classes, the rows with Null values in the columns from the Students table represent the difference between the set of all classes and the set of classes for which students have registered. As we promised, using an OUTER JOIN with a test for Null values is an alternate way to discover the difference between two sets. Unlike a true EXCEPT operation that matches on entire rows from the two sets, you can specify the match in a JOIN operation on just a few specific columns (usually the primary key and the foreign key).
Part 1: How to construct and use SQL OUTER JOINs optimally
