Joining More Than Two Tables

The titleauthor table of the pubs database offers a good example of a situation in which joining more than two tables is helpful. The following query finds the titles of all books of a particular type and the names of their authors:

SELECT au_lname, au_fname, title
FROM authors, titles, titleauthor
WHERE authors.au_id = titleauthor.au_id
    AND titles.title_id = titleauthor.title_id
    AND titles.type = 'trad_cook'

au_lname	au_fname	title
--------------	---------	-----------------------------------
Panteley	Sylvia	Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Blotchet-Halls	Reginald	Fifty Years in Buckingham Palace Kitchens
O'Leary	Michael	Sushi, Anyone?
Gringlesby	Burt	Sushi, Anyone?
Yokomoto	Akiko	Sushi, Anyone?

(5 row(s) affected)

Notice that one of the tables in the FROM clause, titleauthor, does not contribute any columns to the results. Nor do the columns that are joined, au_id and title_id, appear in the results. Nonetheless, this join is possible only by using titleauthor as an intermediate table.

You can also join two or more pairs of columns in the same statement. For example, here's how to find the authors who live in the same city and state as a publisher:

SELECT au_fname, au_lname, pub_name
FROM authors, publishers
WHERE authors.city = publishers.city
    AND authors.state = publishers.state

au_fname	au_lname	pub_name
--------	--------	--------------------
Cheryl	Carson	Algodata Infosystems
Abraham	Bennet	Algodata Infosystems

(2 row(s) affected)

When there is more than one join operator in the same statement, either to join more than two tables or to join more than two pairs of columns, the join expressions can be connected with AND or with OR.