Joining Multiple Tables That Share a Join Key

You may join up to 16 tables in a single SELECT. A common situation in the real world is a database containing many tables with the same join key. To ensure that the server chooses the most efficient access path to your data (by deciding which table will be the outer table and which the inner), you should provide all the relationships between all the tables.

To illustrate this, I’ll join authors, titleauthor, titles, and sales together. I’ve also come up with a reason why you might want to do this type of join.

If you wanted to see how much revenue each author had generated, you would need the author’s name from authors, the price of the book from titles, and the quantity sold from the sales table. Titleauthor must be included to relate authors to sales and titles.

When joining these four tables together, notice that sales, titles, and titleauthor all join on the title_id key. In order to get the best performance from the server, specify all join keys.

/* OLD STYLE */
select  a.au_lname, a.au_fname,
             sum(t.price * s.qty) “Total Revenue”
from         authors a, sales s, titles t, titleauthor ta
where   a.au_id  =  ta.au_id
             and ta.title_id = s.title_id
             and s.title_id = t.title_id
             and ta.title_id = t.title_id
group by au_lname, au_fname
order by “Total Revenue”

/* ANSI STYLE */
select   a.au_lname, a.au_fname,
            sum(t.price * s.qty) “Total Revenue”
from     authors a join titleauthor ta on a.au_id = ta.au_id
            join sales s on ta.title_id = s.title_id
            join titles t on s.title_id = t.title_id and ta.title_id
            = t.title_id
group by au_lname, au_fname
order by “Total Revenue”

au_lname                   au_fname         Total Revenue
-------------------     -------------       -------------
DeFrance                  Michel            119.60
Locksley                  Charlene          199.75
del Castillo              Innes             199.90
Blotchet-Halls            Reginald          239.00
Gringlesby                Burt              299.80
Yokomoto                  Akiko             299.80
Bennet                    Abraham           299.85
Straight                  Dean              299.85
White                     Johnson           299.85
Green                     Marjorie          404.50
Karsen                    Livia             431.80
O’Leary                   Michael           598.55
Carson                    Cheryl            688.50
MacFeather                Stearns           730.55
Panteley                  Sylvia            838.00
Dull                      Ann               1,000.00
Hunter                    Sheryl            1,000.00
Ringer                    Anne              1,302.20
Ringer                    Albert            1,357.60

(19 row(s) affected)

This query joins four tables. Authors joins to titleauthor on au_id; titleauthor, titles, and sales all join to one another on the title_id column. You’ll notice that an extra JOIN clause is provided to give the server the best chance to optimize my query. I provided ta->s, s->t, and ta->t, although I could have gotten these answers with any two of those three.

The query asks for the quantity column in sales to be multiplied by the price column in titles. To do this, the server must first perform the join and then evaluate the sum() aggregate. Sums are performed for rows that join between all four tables and then the results are grouped by the author responsible for the title. In cases where authors collaborated on the same book, all authors get credit for the revenue. (The same money is counted two or three times.)

Summary

Today you learned how to join tables together. Inner joins can be used to gather information from multiple tables or from one table by restricting the rows that appear in a result set using information in another table.

Joins are critically important to understanding the concept of T-SQL; if you go through the quiz and workshop today, and still feel as if you don’t “get it,” spend another day reviewing this chapter. If you still need help, go through tomorrow’s chapter on subqueries. Some people find subqueries a simpler way to understand the join, and in many cases subqueries can do the work of a join.