Joining Multiple Tables

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:

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;

(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;

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;

(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.

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

• INNER JOIN
• Self Join

Cheat sheets

• Joining Multiple Tables