# Joining Tables in T-SQL: an overview

A join puts related rows from two tables side by side, matched on their key columns. An inner join keeps only matches; an outer join also keeps the rows with no match.

Apprentice · T-SQL · SQL · Updated November 15, 2026

By Gregor Dzierzon

## After this lesson you can

- Explain what a join does, and the difference between an inner and an outer join

## Key takeaways

- A join puts related rows from two tables side by side, matching a foreign key such as `Products.CategoryId` to the primary key in `Categories`.
- An inner join keeps only the rows that match: joining `Customers` to `Orders` leaves out the two customers who never ordered.
- A left outer join keeps those rows too, with `NULL` in the other table's columns: 832 rows instead of 830.

Northwind keeps each kind of thing in its own table: products in `Products`, categories in `Categories`. That avoids repeating a category's name on every product. But a report that shows each product **with its category's name** needs both tables at once.

A **join** does that: it puts related rows from two tables side by side, in one result.

## Matching on keys

Tables are related through their keys. Each product has a `CategoryId` (a **foreign key**) that matches the `CategoryId` (the **primary key**) of one row in `Categories`:

| Products.ProductName | Products.CategoryId | | Categories.CategoryId | Categories.CategoryName |
|---|---|---|---|---|
| Chai | 1 | → | 1 | Beverages |
| Chang | 1 | → | 1 | Beverages |
| Aniseed Syrup | 2 | → | 2 | Condiments |

A join walks the arrows: for each product, it finds the category row with the same `CategoryId`, and makes one result row from the two.

## Two kinds of join

The question is what to do with a row that has **no match**.

| Join | Keeps |
|---|---|
| **INNER JOIN** | Only the rows that match in both tables |
| **OUTER JOIN** (`LEFT`, `RIGHT` or `FULL`) | The matches, **plus** the rows from one or both tables that have no match, with `NULL` where the other table's columns would be |

Northwind has two customers who've never placed an order. Join `Customers` to `Orders`:

- An **inner** join leaves those two customers out: there's no order to pair them with. 830 rows, one per order.
- A **left outer** join keeps them, with `NULL`s in the order columns. 832 rows.

The next lessons show both, and then joins across more than two tables.

## Do it: show each product beside its category's name

The product report shows a number where the category should be: `1`, `2`, `3`. The people who read it want the category's name instead.

Two tables hold what the report needs:

- `Products`: each product's `ProductName`, and its `CategoryId`, the foreign key.
- `Categories`: each category's `CategoryId`, the primary key, and its `CategoryName`.

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

| ProductName | CategoryId |
|---|---|
| Chai | 1 |
| Chang | 1 |
| Aniseed Syrup | 2 |
| Chef Anton's Cajun Seasoning | 2 |
| Pavlova | 3 |
| Teatime Chocolate Biscuits | 3 |

| CategoryId | CategoryName |
|---|---|
| 1 | Beverages |
| 2 | Condiments |
| 3 | Confections |

The starter already joins the two tables. Its `INNER JOIN ... ON` line walks the arrows: for each product, it finds the category row with the same `CategoryId`. (The next lesson teaches how to write one.) After the join, the columns of both tables sit side by side, each named `table.column`, such as `Categories.CategoryName`.

For example, Chai's `CategoryId` is `1`, and category `1` is Beverages, so Chai's row in the report is `Chai | Beverages`.

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

| ProductName | CategoryName |
|---|---|
| Aniseed Syrup | Condiments |
| Chai | Beverages |
| Chang | Beverages |
| Chef Anton's Cajun Seasoning | Condiments |
| Pavlova | Confections |
| Teatime Chocolate Biscuits | Confections |

**Your job:** change the starter's query so the report shows each category's name, not its number.

- Show two columns, `ProductName` and `CategoryName`, in that order.
- Keep the starter's `FROM` and `INNER JOIN` lines as they are.
- Sort the rows by `ProductName`, A to Z.

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

## Check yourself

1 question to try on the lesson page.

## So what

Joins are how a database that stores each fact once gives you reports that show everything together. Pick inner when you only want matches, outer when the rows without a match matter too.

## Key terms

- [JOIN](https://www.ask-codey.com/glossary/join-database)

## Cheat sheets

- [Joining Tables in T-SQL: an overview](https://www.ask-codey.com/cheat-sheets/joining-tables-in-t-sql-an-overview-cheat-sheet)

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