//////////////////////
H. Use HASH and MERGE join hints
The following example performs a three-table join among the Product, ProductVendor, and Vendor tables to produce a list of products and their vendors. The query optimizer joins Product and ProductVendor (p and pv) by using a MERGE join. Next, the results of the Product and ProductVendor MERGE join (p and pv) are HASH joined to the Vendor table to produce (p and pv) and v.
Important:
After a join hint is specified, the INNER keyword is no longer optional and must be explicitly stated for an INNER JOIN to be performed.
SELECT p.Name AS ProductName,
v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name,
v.Name;
.
////////////////
///////////////////// PUB Database Examples ////////////////////////////
How to Join Three or More Tables in SQL
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 titleauthor table of the pubs database offers a good example of a situation in which joining more than two tables is helpful. This Transact-SQL query finds the titles of all books of a particular type and the names of their authors:
///////////////////////Copy Code //////////////////
/////////////////////////////
Here is the result set:
au_lname au_fname title
----------------- -------------------- ----------
Blotchet-Halls Reginald Fifty Years in Buckingham Palace
Kitchens
Panteley Sylvia Onions, Leeks, and Garlic:
Cooking Secrets of the Mediterranean
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.
The middle table of the join (the titleauthor table) can be called the translation table or intermediate table, because titleauthor 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.
///////////////////////////
Related Content
