SQL Interview Prep · Lesson

How INNER JOIN Matches Rows

The row-pairing mental model that makes every join question easy.

How INNER JOIN Matches Rows is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The First Join Interviewers Ask

When an interviewer says "join these two tables", they almost always mean INNER JOIN. It is the default join and the one that reveals whether you really understand how rows get paired.

The whole topic reduces to one sentence: an INNER JOIN considers every possible pair of rows from the two tables and keeps only the pairs that satisfy the ON condition. No match means the row simply disappears from the result.

Lock in this mental model and every harder join question gets easier.

Two Tables to Reason About

Throughout this lesson we use two small tables. customers holds who placed orders, and orders records each order with the customer it belongs to.

customers(id, name)
orders(id, customer_id, amount)

The link between them is orders.customer_id = customers.id. That equality is the join predicate the entire result depends on.

customers
id | name
1  | Ada
2  | Bob
3  | Cleo   -- no orders yet

orders
id | customer_id | amount
10 | 1           | 50
11 | 1           | 20
12 | 2           | 99

The Basic INNER JOIN Query

Here is the canonical query an interviewer wants to see. Notice three parts: the two tables, the JOIN ... ON that pairs rows, and the columns you project.

The keyword INNER is optional in every major database. Writing plain JOIN means INNER JOIN, but spelling it out signals intent and avoids ambiguity in a code review.

SELECT c.name, o.amount
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.id;

What the Result Looks Like

Run the query on our sample data and you get three rows. Ada has two orders, so she appears twice. Bob appears once.

Cleo is gone. She has no matching order, so the INNER JOIN drops her entirely. Stating this vanishing of unmatched rows out loud is the most important thing to do in a join interview.

name | amount
Ada  | 50
Ada  | 20
Bob  | 99

The Row-Pairing Mental Model

Imagine the database forming the Cartesian product first: every customer paired with every order. With 3 customers and 3 orders that is 9 candidate pairs.

Then it applies the ON predicate and keeps only the pairs where it is true. Of the 9 candidates, only 3 pass o.customer_id = c.id.

You do not think this way for performance, but it is the exact model that answers every "how many rows come back?" question.

Walking the Candidate Pairs

Let us trace it. The comment after each pair shows whether the predicate is true.

Only the three TRUE rows survive into the result. Every pair involving Cleo (id 3) fails, so she contributes nothing.

-- customer.id vs order.customer_id
(Ada=1, ord10 cust=1)  -> TRUE  keep
(Ada=1, ord11 cust=1)  -> TRUE  keep
(Ada=1, ord12 cust=2)  -> FALSE
(Bob=2, ord10 cust=1)  -> FALSE
(Bob=2, ord12 cust=2)  -> TRUE  keep
(Cleo=3, anything)     -> FALSE (no order matches 3)

INNER JOIN Is Order-Independent

A common probe: does it matter which table you put first? For an INNER JOIN, no. These two queries return identical rows because the ON condition is symmetric.

The optimizer is free to read either table first regardless of how you write it. (This is NOT true for LEFT/RIGHT joins, which come later.)

-- These return the same rows
SELECT c.name, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.id;

SELECT c.name, o.amount
FROM orders o JOIN customers c ON c.id = o.customer_id;

Table Aliases Are Expected

Interviewers want short, clear aliases. Compare the verbose fully-qualified version to the clean aliased one.

Aliases also become mandatory when a column name exists in both tables. Selecting bare id here would raise an ambiguous column error, so you must write c.id or o.id.

-- verbose
SELECT customers.name, orders.amount
FROM customers JOIN orders
  ON orders.customer_id = customers.id;

-- aliased, preferred
SELECT c.name, o.amount
FROM customers c JOIN orders o
  ON o.customer_id = c.id;

Joining on More Than One Column

The ON predicate is not limited to a single equality. When a relationship is defined by a composite key, you AND the conditions together.

Here a price applies per product per region, so the match needs both columns to line up. Interviewers use this to check you have not just memorized single-column joins.

SELECT s.units, p.unit_price
FROM sales s
INNER JOIN price_list p
  ON p.product_id = s.product_id
 AND p.region     = s.region;

Non-Equi Joins Exist Too

The ON condition can be any boolean expression, not just =. A non-equi join uses ranges or inequalities. A classic example buckets a score into a grade band.

Each score matches the one band whose range contains it. Mentioning that joins are not restricted to equality is a quick way to look senior.

SELECT s.student, g.grade
FROM scores s
INNER JOIN grades g
  ON s.points >= g.min_points
 AND s.points <= g.max_points;

Explicit JOIN vs Comma Join

You will still meet the old comma-join syntax in legacy code and some interview questions. These two queries are equivalent for an INNER JOIN.

Prefer the explicit JOIN ... ON form. The comma syntax is easy to mistype into an accidental Cartesian product (by forgetting the WHERE), which is exactly the bug interviewers hope to catch.

-- old implicit join
SELECT c.name, o.amount
FROM customers c, orders o
WHERE o.customer_id = c.id;

-- modern explicit join (preferred)
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Quick Check

Test the core idea of INNER JOIN matching.

Recap: How INNER JOIN Matches

The takeaways to repeat in an interview:

INNER JOIN keeps only matching pairs; unmatched rows on either side disappear.
Mental model: form all candidate pairs, then keep those where the ON predicate is true.
It is order-independent, and the INNER keyword is optional.
ON can use composite keys, ranges, and inequalities, not just a single =.
Use explicit JOIN ... ON with clear aliases to avoid ambiguous-column and accidental-Cartesian bugs.

Frequently asked questions

Is the “How INNER JOIN Matches Rows” lesson free?

Yes — the full text of “How INNER JOIN Matches Rows” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “How INNER JOIN Matches Rows”?

The row-pairing mental model that makes every join question easy. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “How INNER JOIN Matches Rows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep