Joining Three or More Tables
Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.
The ProductVendor table of the AdventureWorks2008R2 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors:
SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
Here is the result set.
Name Name
--------------------------------------------------------
LL Mountain Seat/Saddle Chicago City Saddles
ML Mountain Seat/Saddle Chicago City Saddles
HL Mountain Seat/Saddle Chicago City Saddles
LL Road Seat/Saddle Chicago City Saddles
ML Road Seat/Saddle Chicago City Saddles
HL Road Seat/Saddle Chicago City Saddles
LL Touring Seat/Saddle Chicago City Saddles
ML Touring Seat/Saddle Chicago City Saddles
HL Touring Seat/Saddle Chicago City Saddles
HL Touring Seat/Saddle Expert Bike Co
ML Touring Seat/Saddle Expert Bike Co
LL Touring Seat/Saddle Expert Bike Co
HL Road Seat/Saddle First Rate Bicycles
LL Mountain Seat/Saddle First Rate Bicycles
ML Mountain Seat/Saddle First Rate Bicycles
LL Road Seat/Saddle Hill's Bicycle Service
ML Road Seat/Saddle Hill's Bicycle Service
HL Mountain Seat/Saddle Hybrid Bicycle Center
(18 row(s) affected)
Notice that one of the tables in the FROM clause, ProductVendor, does not contribute any columns to the results. Also, none of the joined columns, ProductID and VendorID, appear in the results. Nonetheless, this join is possible only by using ProductVendor as an intermediate table.
The middle table of the join, the ProductVendor table, can be called the translation table or intermediate table, because ProductVendor is an intermediate point of connection between the other tables involved in the join.
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.