SQL JOIN Multiple Tables

Problem: List all products that have sold. Include supplier and order numbers. Sort by order number.

SELECT OrderNumber, CompanyName, ProductName
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
  JOIN OrderItem I ON P.Id = I.ProductId
  JOIN [Order] O ON O.Id = I.OrderId
 ORDER BY OrderNumber

Note: This is a 4-table join.

OrderNumber	CompanyName	ProductName
542378	Cooperativa de Quesos 'Las Cabras'	Queso Cabrales
542378	Leka Trading	Singaporean Hokkien Fried Mee
542378	Formaggi Fortini s.r.l.	Mozzarella di Giovanni
542379	Mayumi's	Tofu
542379	G'day, Mate	Manjimup Dried Apples
542380	New England Seafood Cannery	Jack's New England Clam Chowder
542380	G'day, Mate	Manjimup Dried Apples
542380	New Orleans Cajun Delights	Louisiana Fiery Hot Pepper Sauce

Multi-Table JOIN syntax.

SELECT column-names
  FROM table-name1 
  JOIN table-name2 ON column-name1 = column-name2
  JOIN table-name3 ON column-name3 = column-name4
  JOIN table-name4 ON column-name5 = column-name6
  ...
 WHERE condition

Multi-Table INNER JOIN syntax.

SELECT column-names
  FROM table-name1 
 INNER JOIN table-name2 ON column-name1 = column-name2
 INNER JOIN table-name3 ON column-name3 = column-name4
 INNER JOIN table-name4 ON column-name5 = column-name6
 ...
 WHERE condition

The INNER keyword is optional.

SELECT O.OrderNumber, S.CompanyName AS Supplier,
       P.ProductName, I.Quantity, I.UnitPrice
  FROM [Order] O 
  JOIN OrderItem I ON O.Id = I.OrderId 
  JOIN Product P ON P.Id = I.ProductId
  JOIN Supplier S ON S.Id = P.SupplierId
 ORDER BY O.OrderNumber

This query performs 3 JOIN operations with 4 tables.
O, I, S, and P are table aliases. Date is a column alias.

OrderNumber	Supplier	ProductName	Quantity	UnitPrice
542378	Cooperativa de Quesos 'Las Cabras'	Queso Cabrales	12	14.00
542378	Leka Trading	Singaporean Hokkien Fried Mee	10	9.80
542378	Formaggi Fortini s.r.l.	Mozzarella di Giovanni	5	34.80
542379	Mayumi's	Tofu	9	18.60
542379	G'day, Mate	Manjimup Dried Apples	40	42.40
542380	New England Seafood Cannery	Jack's New England Clam Chowder	10	7.70
542380	G'day, Mate	Manjimup Dried Apples	35	42.40
542380	New Orleans Cajun Delights	Louisiana Fiery Hot Pepper Sauce	15	16.80
542381	PB Knäckebröd AB	Gustaf's Knäckebröd	6	16.80
542381	Pasta Buttini s.r.l.	Ravioli Angelo	15	15.60
542381	New Orleans Cajun Delights	Louisiana Fiery Hot Pepper Sauce	20	16.80
542382	Specialty Biscuits, Ltd.	Sir Rodney's Marmalade	40	64.80
542382	Norske Meierier	Geitost	25	2.00

• Our Sql JOIN Reference
• Our Sql LEFT JOIN Reference
• Our Sql RIGHT JOIN Reference
• Our Sql JOIN Three Tables Reference