SQL JOIN Multiple Tables

Multi-Table JOIN Explained

A SQL JOIN can be applied to 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 with their supplier and order numbers..

SELECT CompanyName, ProductName, OrderNumber
  FROM Product
  JOIN Supplier ON Supplier.Id = Product.SupplierId
  JOIN OrderItem ON Product.Id = OrderItem.ProductId
  JOIN [Order] ON [Order].Id = OrderItem.OrderId

Note: This query returns 2155 records.

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 the default and is optional.


More Examples

SQL 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 2 JOIN operations with 3 tables.
The O, I, and P are table Aliases. Date is a column Alias.

Result:  2155 records
OrderNumber Date ProductName Quantity UnitPrice
542378 7/4/2012 12:00:00 AM Queso Cabrales 12 14.00
542378 7/4/2012 12:00:00 AM Singaporean Hokkien Fried Mee 10 9.80
542378 7/4/2012 12:00:00 AM Mozzarella di Giovanni 5 34.80
542379 7/5/2012 12:00:00 AM Tofu 9 18.60
542379 7/5/2012 12:00:00 AM Manjimup Dried Apples 40 42.40
542380 7/8/2012 12:00:00 AM Jack's New England Clam Chowder 10 7.70
542380 7/8/2012 12:00:00 AM Manjimup Dried Apples 35 42.40
542380 7/8/2012 12:00:00 AM Louisiana Fiery Hot Pepper Sauce 15 16.80
542381 7/8/2012 12:00:00 AM Gustaf's Knäckebröd 6 16.80
542381 7/8/2012 12:00:00 AM Ravioli Angelo 15 15.60
542381 7/8/2012 12:00:00 AM Louisiana Fiery Hot Pepper Sauce 20 16.80
542382 7/9/2012 12:00:00 AM Sir Rodney's Marmalade 40 64.80
542382 7/9/2012 12:00:00 AM Geitost 25 2.00

You may also like



Guides