join query for multiple table s

We can create more than one join in a query as follows:

SELECT a.Column1, b.Column1, c.Column1
FROM TableA a INNER JOIN
TableB b ON a.AID = b.AID INNER JOIN
TableC c ON b.BID = c.BID
ORDER BY a.Column1, b.Column1

The same query could be rewritten as:

SELECT a.Column1, b.Column1, c.Column1
FROM TableA a JOIN TableB b JOIN TableC c
WHERE a.AID = b.AID
AND b.BID = c.BID
ORDER BY a.Column1, b.Column1


Joining Three or More Tables
SQL Server 2008 R2
Other Versions

* SQL Server "Denali"
* SQL Server 2008
* SQL Server 2005

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:
Copy

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.