Export (0) Print
Expand All

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 AdventureWorks 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.VendorID = v.VendorID
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.

Community Additions

ADD
Show:
© 2015 Microsoft