n-Table Outer Joins

In "n-Table Joins," the third article in my series about joins (February 2002, InstantDoc ID 23733), I discussed writing an n-table inner join. No matter how many tables are involved in an inner join, intersections determine the join, I pointed out; the table order in the FROM clause is irrelevant. Conversely, an outer join always requires direction—LEFT or RIGHT. Yet, when I discussed outer joins between two tables in "The Outer (Join) Limits," November 2001, InstantDoc ID 22689, I noted that order can be irrelevant in two-table outer joins as well because LEFT and RIGHT directions are based only on the two tables that surround the JOIN keyword. (Remember that you always define joins two tables at a time.) Because you could reverse the order of the two tables—and switch the outer join type from LEFT to RIGHT or from RIGHT to LEFT—outer joins between two tables are seemingly straightforward. You'd expect that adding a couple of additional tables to an outer join would be simple. However, when you add a third, fourth, or nth table to an outer join without following certain rules, each additional table might interfere with the earlier outer joins by excluding the outer rows. Let's look at using Venn diagrams as a strategy for helping you write and read complex n-table outer joins with speed and accuracy.

First, a Little Housekeeping
All the examples in this T-SQL Tutor column use the TSQLTutorJoins sample database. If you've kept your TSQLTutorJoins database up-to-date through my three previous columns, you need to execute only the TSQLTutorJoinsUpdate.sql script for this month's additions. If your TSQLTutorJoins database is out of date or needs to be refreshed, run the complete sample database script called TSQLTutorJoinsPartIV.sql to create or recreate the database. (For information about downloading the update and sample-database scripts, see "Obtaining the Code," page 2.) By default and by design, the script will fail if the database already exists. To recreate the database, you have two options: drop the TSQLTutorJoins database manually or modify the script to include the drop. If you want to use the script to perform the drop, be sure to read the comments in the first IF section. After you modify the script, you can use osql.exe to execute this script. For information about why using osql.exe is important for scripts that create large databases, see "All About RAISERROR," December 2001, InstantDoc ID 22980.

A Recap of Venn Diagrams and Outer Joins
As you do in any query, you typically begin a JOIN query by asking a question about related tables. Because SQL Server performs joins two tables at a time, you can illustrate the data that the query returns from each part of the two-table join or the n-table join by using simple circles that overlap to show intersection—a Venn diagram. By using a Venn diagram, you can determine the data set that a particular type of join returns by looking at the data that exists within the various circles and at their intersection. For example, if you want to answer questions about the Customer and Order tables, you can illustrate those two tables as overlapping circles whose intersection represents an inner join. The complete circle on the side that you define in your query as outer represents an outer join.

As Figure 1 shows, the CustomerIDs on the Customer and Order tables have been positioned appropriately to show whether a customer has made a purchase. The placement of CustomerIDs 3, 4, and 7 in the outer portion of the Customer table shows that they're customers who haven't made a purchase. CustomerIDs 1, 2, 5, and 6 appear in the overlapping section, meaning that these customers have placed at least one order. In fact, CustomerIDs 2 and 6 have placed more than one order (i.e., the intersection includes a row for every match). Finally, the NULL values in the outer portion of the Order table represent over-the-counter orders, which don't require customer information. Using this diagram, you can anticipate which rows and the number of rows each type of query returns.

To answer the question, Which customers have placed an order? you'd use an inner join between the Customer and Order tables. The overlapping section in Figure 1 represents this query, which returns CustomerIDs 1, 2, 5, and 6. Listing 1 shows the INNER JOIN query that answers the question, Which customers have placed an order?

To answer the same question (Which customers have placed an order?) yet include all customers whether or not they've placed an order, you'd use an OUTER JOIN query in which Customer is the outer table. In the Venn diagram that Figure 2 shows, the circle on the left represents this query, which returns the same CustomerIDs (1, 2, 5, and 6) as the query in Figure 1 returned. But the query also returns CustomerIDs 3, 4, and 7, which appear in the outer portion of the left circle. Listing 2 shows the LEFT OUTER JOIN query that returns all customers—whether or not they've placed an order—as well as all customers who have placed an order. The left outer join returns nine rows—the six rows from the intersection as well as the three customers who've made no purchases—CustomerIDs 3, 4, and 7. (Note: If you've returned only eight rows, you're probably missing CustomerID 7, the new customer. See the housekeeping section near the beginning of this article to add this new customer or update your TSQLTutorJoins database.) As a result of this modification, the sample database has seven customers—four of whom have made purchases and three of whom have not.