Dofactory.com
Dofactory.com

SQL JOIN Multiple Tables

An SQL query can JOIN multiple tables.

For each new table an extra JOIN condition is added.

Multi-Table JOINs work with SELECT, UPDATE, and DELETE queries.

Example

#

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.

Result:  2,155 records
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

Syntax

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.


More Examples

JOIN with Multiple Tables

Problem: List all orders, sorted by order number, with supplier names, product names, quantities, prices.
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

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.

Result:  2,155 records
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

You may also like



Last updated on Dec 21, 2023