Joining Multiple Tables

Joins chain: each JOIN adds one more table, linked by its key to a table already in the query. A many-to-many link goes through its associative table, and a table can even join to itself.

Journeyman · T-SQL · SQL · Updated

By

After this lesson you can

  • Join three or more tables in one query, following the keys from table to table
  • Join a table to itself, with two aliases

Key takeaways

  • Each JOIN adds one more table, and its ON links it by key to a table already in the query.
  • Orders and products are many-to-many, so a query that needs both joins through their associative table, OrderDetails.
  • Trace the path of keys from the table you start with to the one that holds the answer, then write one JOIN per step.
  • A self join joins a table to itself with two aliases, such as e for the employee and m for the manager.
  • Make a self join a LEFT JOIN when some rows have no match, so Andrew Fuller, who reports to no one, still appears.

You can join as many tables as a question needs. Each JOIN adds one more table, and its ON links it to a table that's already in the query.

Following the keys

What has Alfreds Futterkiste ordered, and in which categories? The answer is spread over five tables:

Table Gives Linked by
Customers The company name
Orders The orders CustomerId to Customers
OrderDetails Each order's lines OrderId to Orders
Products Each line's product ProductId to OrderDetails
Categories Each product's category CategoryId to Products

OrderDetails is the associative table from Many-to-Many: it links orders and products, which are many-to-many.

SELECT c.CompanyName, o.OrderId, p.ProductName, cat.CategoryName, od.Quantity
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerId = o.CustomerId
INNER JOIN OrderDetails AS od
    ON o.OrderId = od.OrderId
INNER JOIN Products AS p
    ON od.ProductId = p.ProductId
INNER JOIN Categories AS cat
    ON p.CategoryId = cat.CategoryId
WHERE c.CustomerId = 'ALFKI'
ORDER BY o.OrderId, p.ProductName;
CompanyName OrderId ProductName CategoryName Quantity
Alfreds Futterkiste 10643 Chartreuse verte Beverages 21
Alfreds Futterkiste 10643 Spegesild Seafood 2
Alfreds Futterkiste 10692 Vegie-spread Condiments 20
Alfreds Futterkiste 10702 Aniseed Syrup Condiments 6
Alfreds Futterkiste 10835 Raclette Courdavault Dairy Products 15

(12 rows in all.) Read it as a chain: customers to their orders, orders to their lines, lines to their products, products to their categories. Each ON takes one step along the keys.

Grouping across tables

Joined tables can be grouped like any other. Sales per category, from the order lines up to the category names:

SELECT cat.CategoryName, SUM(od.UnitPrice * od.Quantity) AS Sales
FROM OrderDetails AS od
INNER JOIN Products AS p
    ON od.ProductId = p.ProductId
INNER JOIN Categories AS cat
    ON p.CategoryId = cat.CategoryId
GROUP BY cat.CategoryName
ORDER BY Sales DESC;
CategoryName Sales
Beverages 286526.95
Dairy Products 251330.50
Meat/Poultry 178188.80
Confections 177099.10
Seafood 141623.09
Condiments 113694.75
Produce 105268.60
Grains/Cereals 100726.80

A table joined to itself

Each employee's ReportsTo holds the EmployeeId of their manager, another row in the same table. To show each employee next to their manager's name, join Employees to itself, with two aliases: e for the employee, m for the manager:

SELECT e.FirstName + ' ' + e.LastName AS Employee
    , m.FirstName + ' ' + m.LastName AS Manager
FROM Employees AS e
LEFT JOIN Employees AS m
    ON e.ReportsTo = m.EmployeeId
ORDER BY e.EmployeeId;
Employee Manager
Nancy Davolio Andrew Fuller
Andrew Fuller NULL
Janet Leverling Andrew Fuller
Margaret Peacock Andrew Fuller
Steven Buchanan Andrew Fuller
Michael Suyama Steven Buchanan

(9 rows in all.) This is a self join. It's a LEFT JOIN so that Andrew Fuller, who reports to no one, still appears.

Do it: list what Ana Trujillo's shop ordered

Ana Trujillo's shop, whose CustomerId is 'ANATR', has asked for a list of every product it has ordered: which order, which product, and how many.

The answer is spread over three tables:

  • Orders: each order's OrderId, and the CustomerId of the customer who placed it.
  • OrderDetails: one row per order line, with its OrderId, the ProductId it sold, and the Quantity.
  • Products: each product's ProductId and ProductName.

They hold these rows (the Tables tab shows the SQL that makes them):

OrderId CustomerId
10308 ANATR
10355 AROUT
10625 ANATR
OrderId ProductId Quantity
10308 69 1
10308 70 5
10355 24 25
10355 57 25
10625 14 3
10625 42 5
10625 60 10
ProductId ProductName
14 Tofu
24 Guaraná Fantástica
42 Singaporean Hokkien Fried Mee
57 Ravioli Angelo
60 Camembert Pierrot
69 Gudbrandsdalsost
70 Outback Lager

Follow the keys for one line. Order 10308 is ANATR's. Its first line in OrderDetails sold product 69, 1 of it. Product 69 is Gudbrandsdalsost. So one row of the answer is 10308 | Gudbrandsdalsost | 1.

Step Table Linked by
1 Orders: the customer's orders
2 OrderDetails: each order's lines OrderId to Orders
3 Products: each line's product name ProductId to OrderDetails

Your query should give exactly these rows, in this order:

OrderId ProductName Quantity
10308 Gudbrandsdalsost 1
10308 Outback Lager 5
10625 Camembert Pierrot 10
10625 Singaporean Hokkien Fried Mee 5
10625 Tofu 3

Your job: write one SELECT query that joins the three tables.

  • Start from Orders, join OrderDetails on OrderId, then join Products on ProductId.
  • Show three columns, OrderId, ProductName and Quantity, in that order.
  • Keep only the orders whose CustomerId is 'ANATR'.
  • Sort the rows by OrderId, then by ProductName, A to Z.

OrderId is in two of the tables and ProductId in two, so say which table you mean each time you use one of them.

Practiced live on the lesson page: write the SQL right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

Any question, however many tables it spans, is a chain of joins along the keys. Sketch the path from the table you start with to the one that holds the answer, then write one JOIN per step.

Key terms

Cheat sheets