n-Table Joins

Although n-table joins—joins between more than two tables—might seem more complex than two-table joins, n-table joins are just a series of two-table joins. Using Venn diagrams and database diagramming, you can predict and write complex n-table joins with greater ease and understanding.

n-Table Inner Joins
Before you write an n-table inner join, you must first analyze the tables that have the data you need. You only have to qualify the ambiguous columns, but if you qualify all the columns, you make more complex code easier to read.

To know what to output, you need a query and a question to answer about the data. Using the TSQLTutorJoins database that I provided as a download in "Joins 101," October 2001, InstantDoc ID 22071, ask the question, What categories of products have you sold to which customers?

After you analyze the tables, you can write the FROM clause; I recommend stepping through it slowly. Create a database diagram of the tables you're interested in, and let Enterprise Manager's Database Diagramming Wizard create a diagram of all related tables. If you use the wizard to add related tables, make sure you select the Add related tables automatically check box before you choose any tables. The tables you select, along with any related tables, will show up in the Tables to add to diagram box. You add three tables to the Tables to add to diagram box: Category, Order, and Product. You add the Customer table only to the Tables to add to diagram box because the Customer table isn't related to any tables other than those already listed. Figure 1 shows the Database Diagram as it looks before you make any of the modifications I discuss in this column.

After the wizard has diagrammed the tables you're interested in (and their related tables), you can see the correct number of tables to list—but not how to order them—in the FROM clause. In an inner join, the order in which you list the tables doesn't affect performance. However, when you're writing an n-table join, each table listed—except for the first table—must join to a table already listed in the FROM clause.

Let's look at a join between the Order, Product, and Category tables. If you joined these three tables together in an inner join, you couldn't list the Order table immediately followed by the Category table because these two tables have nothing relevant in common (they don't have a JOIN condition). However, you could list the Order, Product, and Category tables in one of the four ways that Figure 2 shows.

The Database Diagram shows that four tables in the TSQLTutorJoins database are relevant to the question, What categories of products have you sold to which customers? You can write the join in your FROM clause in many possible ways; however, for simplicity, I've listed only the two most logical (based on the flow of the relationships in the Database Diagram), which Figure 3 shows.

Using the four tables that Figure 3 shows, let's create a simple FROM clause without JOIN conditions:

FROM dbo.Customer AS Cust
  inner join dbo.[Order] AS O
  inner join dbo.Product AS P
  inner join dbo.Category AS Cat

Next, you need to thoroughly review the potential JOIN conditions among the tables, then add the applicable JOIN conditions to the FROM clause, which Listing 1, page 12, shows. If you're not sure what these JOIN conditions are, use the Database Diagram to determine the proper conditions. Using the Database Diagram in Figure 1, you right-click any relationship line between two tables to bring up a list of three options: Delete Relationship from Database, Show Relationship Labels, and Properties. Select Show Relationship Labels first to put the relationship names above each relationship line. Next, right-click the relationship line between the Order and Product tables, then select Properties. Your screen should look like the one that Figure 4, page 12, shows.

To determine the JOIN conditions, review the information displayed in the Primary key table and the Foreign key table sections on the Relationships tab of the Properties dialog box. The Primary key table is Product, and the Foreign key table is Order. The columns that define this primary key­foreign key relationship are ProductID in the Product table and ProductID in the Order table. So, your JOIN condition is Product.ProductID = Order.ProductID (alias names replace the table names in the actual query).

In this particular case, your tables easily join because a simple path—only one path—exists from end to end. In other words, reviewing the diagram and starting with one end (Customer or Category), each table only goes to one table. From the Customer table, you go to the Order table; from the Order table, you go to the Product table; and from the Product table, you go to the Category table (following the relationship lines). Progressively, each table joins to only one other table. However, some databases might have multiple paths and, therefore, multiple JOIN conditions. For example, when the right conditions exist, a common database design strategy that can improve performance and minimize the number of tables that the JOIN condition requires is to add redundant foreign keys.

Redundant foreign keys are columns that you add to a table to describe a relationship that might be numerous tables away. In the TSQLTutorJoins database, you could add a redundant foreign key to the Order table to describe the relationship between an order and the category of the product ordered. Remember, in this database, orders are simple orders—only one product is sold on an order—so, each order relates to only one category.