What is an OUTER JOIN?
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).
TABLE OF CONTENTS
|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.|
This was first published in March 2008