SQL JOIN 3 Tables

3-Table JOIN Explained

A SQL JOIN can be applied to 3 tables.

Simply add a extra JOIN condition for the third table.

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

Example

#

Problem: List all suppliers with products that have sold.

SELECT DISTINCT CompanyName, ProductName
  FROM Supplier 
  JOIN Product ON Supplier.Id = Product.SupplierId
  JOIN OrderItem ON Product.Id = OrderItem.ProductId

Note: This query returns 77 records.
Removing the second JOIN returns 78 records, because only 1 product never sold.

Syntax

3-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
 WHERE condition

3-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
 WHERE condition

The INNER keyword is the default and is optional.
INNER JOINs are the most commonly used type of JOIN.


More Examples

SQL SELECT JOIN with 3 Tables

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

SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date, 
       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
 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