The Danger of Cartesian Products

If you unwittingly create a Cartesian product on even moderately sized tables, you are in for a long wait. Using ISQL/w, you can cancel a query by clicking the red CANCEL button in the upper-right corner. If you don’t cancel, though, and patiently wait for your data, or if you are using a different query tool that does not support the CANCEL feature, you might wait a long time. Table 7.1 gives some examples of how many rows you could expect from a Cartesian join in a mythical database.

Accidental Cartesian joins are a great argument in favor of the ANSI syntax.

Joining Multiple Tables

Sometimes, when you want to pull information from multiple tables that are conceptually related, you will find that they do not share a join key. The authors table and titles table do not have any columns in common, as you can see from Figure 7.2. The little keys next to columns indicate that they make up the primary key for that table.

Figure 7.2.  Authors and titles cannot be directly joined because they have nothing in common.

A typical question you might ask in the pubs database is, “Which authors wrote which books?” To join these two tables together, there needs to be a third, associative table that contains join keys to both.

That table is titleauthor. The titleauthor table, seen in Figure 7.3, references the primary keys in titles and in authors. It also provides some data on royalty split information and which author appears first on the book. More importantly (for the purposes of this query, anyway: the authors themselves are probably much more concerned with the last two columns), it provides a method for joining authors to titles. It serves as the bridge between the tables.

Figure 7.3.  With the help of the associative table, titleauthor, titles can be joined to authors in a meaningful way.

This query lists all the authors with the books they have written. In this query, you don’t want information from the titleauthor table, but you must specify it and its join keys to the server so that it knows how to relate all of the tables.

Because an author could have written many books, and a book could have been written by many authors, some books and authors appear multiple times in the result set.

/* OLD STYLE */
select    a.au_lname, a.au_fname, convert(char(20), t.title) “Title”
from      authors a, titleauthor  ta,  titles  t
where     a.au_id  =  ta.au_id
           and ta.title_id  =  t.title_id
order by Title

/* ANSI STYLE */
select    a.au_lname, a.au_fname, convert(char(20), t.title) “Title”
from      authors  a  inner join titleauthor ta on a.au_id = ta.au_id
           inner   join titles t on ta.title_id = t.title_id
order  by  Title

au_lname         au_fname                Title
------------    ----------            ---------------
Carson           Cheryl               But Is It User Frien
MacFeather       Stearns              Computer Phobic AND
Karsen           Livia                Computer Phobic AND
O’Leary          Michael              Cooking with Compute
MacFeather       Stearns              Cooking with Compute
Locksley         Charlene             Emotional Security:
Blotchet-Halls   Reginald             Fifty Years in Bucki
Ringer           Anne                 Is Anger the Enemy?
Ringer           Albert               Is Anger the Enemy?
Ringer           Albert               Life Without Fear
Locksley         Charlene             Net Etiquette
Panteley         Sylvia               Onions, Leeks, and G
White            Johnson              Prolonged Data Depri
Dull             Ann                  Secrets of Silicon V
Hunter           Sheryl               Secrets of Silicon V
del Castillo     Innes                Silicon Valley Gastr
Straight         Dean                 Straight Talk About
O’Leary          Michael              Sushi, Anyone?
Gringlesby       Burt                 Sushi, Anyone?
Yokomoto         Akiko                Sushi, Anyone?
Green            Marjorie             The Busy Executive’s
Bennet           Abraham              The Busy Executive’s
DeFrance         Michel               The Gourmet Microwav
Ringer           Anne                 The Gourmet Microwav
Green            Marjorie             You Can Combat Compu

(25 row(s) affected)

Authors are listed next to books they wrote. Where multiple authors worked on the same book, such as with Sushi, Anyone?, each author is listed with the book, and the book appears several times. Authors who have written two or more books, such as Albert Ringer, appear more than once, as they are listed next to each book they wrote.

This report is ordered by the title, so it is easy to see books that were written by more than one author. Ordering the same query by au_lname would produce a report with the same data, but it would be easier to see many titles written by each author.

Note that in the ANSI syntax, the inner joins are not separated by commas. The FROM clause is one big statement.

One final note: The order in which you specify the tables in either syntax has no impact on performance. The server will rearrange the tables in the most effective join order.