INNER JOIN
Combining rows from two tables on a matching condition — the single most-used multi-table operation in SQL.
What you'll learn
- INNER JOIN syntax, and what 'matching' really means
- Why join keys belong in ON, not WHERE
- Table aliases and qualifying ambiguous columns
- Why a join can return more rows than either input table
Before you start
Real data lives spread across many tables — users in one, their orders in another — and the answers you actually need require combining them. A JOIN matches a row from one table to a row in another, based on a condition you give it.
INNER JOIN (often written just JOIN) keeps only the rows where the condition matches in both tables. No match means no output row at all — not even a NULL-filled placeholder. Picture the keys of each table as two overlapping circles; an inner join keeps only the overlap:
Matching two tables
Here are our two tables. Each order points back to a user through user_id:
| users id | name | country |
|---|---|---|
| 1 | Asha | IN |
| 2 | Bo | CN |
| 3 | Carlos | US |
| orders id | user_id | product | qty |
|---|---|---|---|
| 101 | 1 | Pencil | 3 |
| 102 | 1 | Laptop | 1 |
| 103 | 3 | Coffee | 2 |
SELECT u.name, u.country, o.id AS order_id, o.product, o.qty
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY o.id;| name | country | order_id | product | qty |
|---|---|---|---|---|
| Asha | IN | 101 | Pencil | 3 |
| Asha | IN | 102 | Laptop | 1 |
| Carlos | US | 103 | Coffee | 2 |
Read the join as: “for each user, find the orders where o.user_id = u.id, and emit one combined row per match.” Three things to notice. Bo has vanished — she placed no orders, so the inner join drops her. Asha appears twice — she has two orders, and each match makes a row. And o.user_id = u.id is the join key, the relationship that says which rows belong together.
A couple of habits make joins readable: give each table a short alias (users u, orders o), and qualify every column with it (u.name, not name) so that a column existing in both tables is never ambiguous.
Keys go in ON, filters go in WHERE
You can write the join condition in WHERE (FROM users u, orders o WHERE o.user_id = u.id), and for an inner join the result is identical. Don’t, though — keep join keys in ON and row filters in WHERE. It states intent clearly, and (as the next lesson shows) for outer joins putting the join key in WHERE silently turns the outer join back into an inner one. A WHERE after the join still works on any column from any joined table — WHERE u.country = 'IN' would keep just Asha’s rows.
Practice
Quick check
Questions about this lesson
What does an INNER JOIN do?
An INNER JOIN combines rows from two tables wherever the join condition matches, keeping only the matching rows from both sides. Rows with no match on either table are dropped from the result.
What's the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only matching rows; LEFT JOIN keeps all rows from the left table and fills NULLs where the right table has no match. Use LEFT JOIN when you need to preserve every record from your primary table.
Why am I getting duplicate rows after a join?
A join multiplies rows when the key isn't unique on one side — each left row matches several right rows. Check the key's uniqueness, aggregate to the right grain first, or tighten the condition so each match is one-to-one.
Practice this in an interview
All questionsA CROSS JOIN produces the Cartesian product of two tables — every row from the left paired with every row from the right — giving M x N output rows with no join condition. It is useful for generating date spines, creating all combinations of dimension values, or populating test data grids.
You combine conditions in the ON clause with AND to join on multiple columns, which is necessary when no single column is a unique identifier across both tables. For index performance, the most selective column — or the column used in equality predicates — should come first in a composite index.
Join order never affects the logical result — SQL is declarative and the engine chooses the physical join order. However, join order in the query does influence the optimiser's starting point, and in complex queries with many tables or when statistics are stale, manually reordering joins or using query hints can significantly change performance.
A non-equi join uses inequality operators (BETWEEN, >=, <, !=) in the ON clause instead of or in addition to equality. They are correct and valid SQL, but they prevent hash-join and merge-join plans, often forcing a nested-loop join that scales quadratically — so they require careful indexing or pre-filtering at scale.
A self-join joins a table to itself, typically to compare rows within the same dataset — classic use cases are finding employee-manager relationships in a single table, detecting duplicate rows, or comparing a row to the previous/next row when window functions are unavailable.
A semi-join returns each row from the left table at most once when at least one match exists on the right, without returning any columns from the right table. An INNER JOIN can duplicate left rows when the right side has multiple matches. In SQL, semi-joins are written with EXISTS or IN subqueries.