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

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

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