# 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 November 3, 2026

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

```sql
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:

```sql
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:

```sql
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.

## 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](https://www.ask-codey.com/glossary/inner-join)
- [Self Join](https://www.ask-codey.com/glossary/self-join)

## Cheat sheets

- [Joining Multiple Tables](https://www.ask-codey.com/cheat-sheets/joining-tables-in-t-sql-joining-multiple-tables-cheat-sheet)

Source: https://www.ask-codey.com/lessons/joining-tables-in-t-sql-joining-multiple-tables