SQL guide · Joins

SQL INNER JOIN: Combine Rows That Match in Both Tables

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

Browse all SQL guides

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