# INNER JOIN

INNER JOIN combines two tables on a matching column, keeping only rows that match. Table aliases keep it short, and the joined columns can be filtered, grouped and sorted like any others.

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

By Gregor Dzierzon

## After this lesson you can

- Join two tables with INNER JOIN ... ON, using table aliases
- Filter, group and sort joined rows, using columns from both tables

## Key takeaways

- `INNER JOIN ... ON` names the second table and the columns that must match, such as `ON p.CategoryId = c.CategoryId`.
- When a column name is in both tables, put the table name or alias in front of it, such as `p.CategoryId`.
- Give each table a short alias with `AS`, and once it has one, use the alias everywhere in that query.
- The joined result works like one table: filter, group and sort it using columns from both tables.
- Rows with no match disappear: joining `Customers` to `Orders` leaves out the two customers who never ordered.

**`INNER JOIN`** combines two tables, keeping only the rows that match. You name the second table, and say **which columns must match** with `ON`:

```sql
SELECT TOP (4) Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories
    ON Products.CategoryId = Categories.CategoryId
ORDER BY Products.ProductId;
```

| ProductName | CategoryName |
|---|---|
| Chai | Beverages |
| Chang | Beverages |
| Aniseed Syrup | Condiments |
| Chef Anton's Cajun Seasoning | Condiments |

Every product has a category, so without the `TOP`, all 77 products come back, each with its category's name.

## Naming columns: table.column

Both tables have a `CategoryId` column, so `ON CategoryId = CategoryId` would be meaningless. Write the table's name first: `Products.CategoryId`. When a column name exists in both tables, you must say which you mean, anywhere in the query; when it's in only one, the table name is optional, but clearer.

## Table aliases

Typing `Products.` and `Categories.` everywhere gets long. Give each table a short **alias** with `AS`, and use it instead:

```sql
SELECT TOP (4) p.ProductName, c.CategoryName, p.UnitPrice
FROM Products AS p
INNER JOIN Categories AS c
    ON p.CategoryId = c.CategoryId
WHERE c.CategoryName = 'Seafood'
ORDER BY p.UnitPrice DESC;
```

| ProductName | CategoryName | UnitPrice |
|---|---|---|
| Carnarvon Tigers | Seafood | 62.50 |
| Ikura | Seafood | 31.00 |
| Gravad lax | Seafood | 26.00 |
| Nord-Ost Matjeshering | Seafood | 25.89 |

Once a table has an alias, use the alias everywhere in that query. (`INNER JOIN` can also be written as just `JOIN`; this course writes `INNER` to be clear.)

## Joins with everything else

The joined result works like one big table: filter it with `WHERE` (as above, by the category's name), sort it with `ORDER BY`, and group it. Products per category, now with names instead of ids:

```sql
SELECT c.CategoryName, COUNT(*) AS Products, AVG(p.UnitPrice) AS AveragePrice
FROM Products AS p
INNER JOIN Categories AS c
    ON p.CategoryId = c.CategoryId
GROUP BY c.CategoryName
ORDER BY Products DESC, c.CategoryName;
```

| CategoryName | Products | AveragePrice |
|---|---|---|
| Confections | 13 | 25.16 |
| Beverages | 12 | 37.9791 |
| Condiments | 12 | 23.0625 |
| Seafood | 12 | 20.6825 |
| Dairy Products | 10 | 28.73 |
| Grains/Cereals | 7 | 20.25 |
| Meat/Poultry | 6 | 54.0066 |
| Produce | 5 | 32.37 |

## Rows without a match disappear

An inner join keeps only matches. Join `Customers` to `Orders` and count:

```sql
SELECT COUNT(*) AS Rows
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerId = o.CustomerId;
```

| Rows |
|---|
| 830 |

One row per order, 830 in all. The two customers with no orders aren't there: there was nothing to pair them with. Often that's what you want. When it isn't, you need an outer join.

## Do it: list the products from suppliers in Japan

A buyer is planning a trip to Japan and wants a list of the products that come from suppliers there, each beside its supplier's name.

Two tables hold what the list needs:

- `Products`: each product's `ProductId`, `ProductName`, and `SupplierId`, a foreign key.
- `Suppliers`: each supplier's `SupplierId`, the primary key, its `CompanyName` and its `Country`.

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

| SupplierId | CompanyName | Country |
|---|---|---|
| 1 | Exotic Liquids | UK |
| 4 | Tokyo Traders | Japan |
| 6 | Mayumi's | Japan |
| 7 | Pavlova, Ltd. | Australia |

| ProductId | ProductName | SupplierId |
|---|---|---|
| 1 | Chai | 1 |
| 2 | Chang | 1 |
| 3 | Aniseed Syrup | 1 |
| 9 | Mishi Kobe Niku | 4 |
| 10 | Ikura | 4 |
| 13 | Konbu | 6 |
| 14 | Tofu | 6 |
| 15 | Genen Shouyu | 6 |
| 16 | Pavlova | 7 |
| 74 | Longlife Tofu | 4 |

For example, Ikura's `SupplierId` is `4`. Supplier `4` is Tokyo Traders, in Japan, so Ikura is on the list, beside Tokyo Traders. Chai's supplier is `1`, Exotic Liquids, in the UK, so Chai isn't.

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

| ProductName | CompanyName |
|---|---|
| Genen Shouyu | Mayumi's |
| Ikura | Tokyo Traders |
| Konbu | Mayumi's |
| Longlife Tofu | Tokyo Traders |
| Mishi Kobe Niku | Tokyo Traders |
| Tofu | Mayumi's |

**Your job:** write one `SELECT` query that joins `Products` to `Suppliers` with `INNER JOIN`.

- Show two columns, `ProductName` and `CompanyName`, in that order.
- Match each product's `SupplierId` to the supplier's `SupplierId`.
- Keep only the rows whose supplier's `Country` is `'Japan'`.
- Sort the rows by `ProductName`, A to Z.

Both tables have a `SupplierId` column, so say which table you mean each time you use it: `Products.SupplierId`, or `p.SupplierId` once `Products` has the alias `p`.

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

`INNER JOIN ... ON` puts related rows side by side, and from then on it's a table like any other: filter it, group it, sort it. Most reports you'll ever write start with one.

## Key terms

- [INNER JOIN](https://www.ask-codey.com/glossary/inner-join)
- [Join Condition](https://www.ask-codey.com/glossary/join-condition)
- [Alias](https://www.ask-codey.com/glossary/alias)

## Cheat sheets

- [INNER JOIN](https://www.ask-codey.com/cheat-sheets/joining-tables-in-t-sql-inner-join-cheat-sheet)

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