# SQL INNER JOIN: Combine Rows That Match in Both Tables

- URL: https://betteranalyst.com/sql/inner-join

SQL guide · Joins

An `INNER JOIN` combines rows from two tables by comparing a column in one table with a column in the other. For every pair of rows where the join condition is true, the database produces one combined row. Rows that have no partner on the other side are left out of the result entirely, which is why this is the join you reach for when you only care about records that exist in both places.

The most common use is following a foreign key. An `orders` table stores a `customer_id`, and the customer name lives in `customers`. Joining the two on that key gives you one result set that lists each order next to the customer who placed it. Without the join you would have to run two queries and stitch the results together in application code.

In standard SQL the keyword `INNER` is optional, so `JOIN` on its own means the same thing. The older comma syntax (`FROM a, b WHERE a.id = b.a_id`) still works, but the explicit `JOIN ... ON` form keeps the join logic separate from the row filters and is much harder to get wrong. You can chain as many joins as you need, and each one adds another table to the result.

## Basic syntax

```
SELECT t1.column_a, t2.column_b
FROM table1 AS t1
INNER JOIN table2 AS t2
  ON t1.key_column = t2.key_column
[WHERE condition]
[ORDER BY column_a];
```

The `ON` clause holds the join condition, usually an equality between a key in each table. Table aliases such as `t1` and `t2` shorten the query and let you qualify column names, which is required whenever both tables contain a column with the same name. The optional `WHERE` clause filters the joined rows after they have been matched.

## SQL INNER JOIN examples

### Orders with the customer name

This joins `orders` to `customers` on the customer key. Each order row appears once with the matching customer name, and orders whose `customer_id` has no matching customer are dropped from the output.

```
SELECT o.order_id, o.order_date, c.customer_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;
```

### Joining three tables

Joins can be chained. Here `order_items` links each order to a product, so the query walks from orders to line items to product names. Each additional `INNER JOIN` narrows the result to rows that match at every step.

```
SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders AS o
INNER JOIN customers AS c ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi ON oi.order_id = o.order_id
INNER JOIN products AS p ON p.product_id = oi.product_id
WHERE o.order_date >= '2026-01-01';
```

### Joining and aggregating

A join is often followed by `GROUP BY`. This totals the revenue per customer by multiplying quantity by price on each line item. Only customers that have at least one order line show up in the result.

```
SELECT c.customer_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY c.customer_name
ORDER BY revenue DESC;
```

### Self join on one table

A table can be joined to itself by giving it two aliases. This pairs every employee with their manager, using the `manager_id` column that points back at `employee_id` in the same table.

```
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.employee_id;
```

## Where SQL dialects differ

**PostgreSQL:** Supports `INNER JOIN` and the shorthand `JOIN`, plus `USING (column)` and `NATURAL JOIN`. With `USING`, the shared column appears only once in `SELECT *` output.

**MySQL:** Accepts `INNER JOIN`, `JOIN` and `CROSS JOIN` interchangeably when an `ON` clause is present, and supports `USING`. In `UPDATE` and `DELETE` statements you can join tables directly, which is a MySQL extension.

**SQL Server:** Uses the same `INNER JOIN ... ON` form. There is no `USING` clause, so you must always write the full `ON` condition. Joins in `UPDATE` use a `FROM` clause with the target alias.

**SQLite:** Supports `INNER JOIN`, `JOIN`, `USING` and `NATURAL JOIN`. Foreign keys are not enforced by default, so orphan rows are possible and will silently disappear from an inner join.

**Oracle:** Supports ANSI `INNER JOIN` since 9i. Older code uses the comma syntax with the condition in `WHERE`, and the legacy `(+)` operator for outer joins, so you will see both styles in existing queries.

## Common mistakes

- Forgetting the `ON` condition or writing it incorrectly. Without a correct condition you can end up with a cross product, where every row of one table is paired with every row of the other.
- Expecting unmatched rows to appear. An inner join silently drops customers without orders and orders without customers, so counts can look smaller than the source tables. Use `LEFT JOIN` when you need to keep every row from one side.
- Joining on a column that is not unique on one side. A customer with three orders appears three times, so totals taken from the customer table get multiplied.
- Leaving ambiguous column names unqualified. If both tables have an `id` or `name` column, most databases raise an ambiguity error, so prefix columns with the table alias.
- Putting filters on the joined table inside `WHERE` when thinking about outer joins. For an inner join this is harmless, but the habit causes bugs once the join is changed to a `LEFT JOIN`.

## Related SQL guides

- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)
- [SQL RIGHT JOIN](https://betteranalyst.com/sql/right-join)
- [SQL FULL OUTER JOIN](https://betteranalyst.com/sql/full-outer-join)
- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)