What does an INNER JOIN ... ON return, and which rows does it silently drop?
answer
- Think about what does not come back
- Two behaviours: dropping and multiplying
- Conceptually pair everything, then keep TRUE
- No NULL-extension — that is the other join
- Unmatched rows on both sides disappear
basics
~20 sINNER JOIN emits one output row for every pair of left and right rows whose ON predicate evaluates to TRUE. Any row on either side with no qualifying partner is dropped from the result, with no warning and no NULL placeholder.
solid answer
~50 sConceptually, `A INNER JOIN B ON p` forms every combination of a row from A with a row from B and keeps the combinations where `p` is TRUE. Everything else disappears: a left row with no partner, a right row with no partner, and any pair whose predicate evaluates to FALSE **or** to UNKNOWN (comparisons involving NULL are UNKNOWN, and UNKNOWN is not TRUE). Nothing is NULL-extended — that is what outer joins do. Because the rule is symmetric, `A INNER JOIN B` and `B INNER JOIN A` return the same set of rows, just with the columns in a different order. `INNER` is optional: a bare `JOIN ... ON` is an inner join. The practical consequence is the one interviewers are probing: an inner join is also a **filter**, so adding one to a query can quietly reduce the row count.
go deeper
Be ready to state the rule in one sentence and predict the output of a small two-table join by hand, including which rows go missing. Know that INNER is optional.
Explain the pair-and-filter model, why a predicate that is UNKNOWN drops the pair just as FALSE does, and why the operator is commutative while outer joins are not.
Show that you treat every inner join as an implicit filter: quantify the rows a new join removes before shipping a report, and decide deliberately between dropping and preserving unmatched rows.
Frame it as a contract question — whether missing lookup rows are a data-quality defect to fix upstream or a legitimate state the query must tolerate, and make that choice explicit in the team's query conventions.
## The definition `A INNER JOIN B ON p` is defined in two conceptual steps. First, pair every row of `A` with every row of `B` (the Cartesian product). Second, keep only the pairs for which the join predicate `p` evaluates to TRUE. Each surviving pair becomes one output row whose columns are the columns of `A` followed by the columns of `B`. This is a *logical* definition of the result, not a description of how an engine computes it — nothing here says the database actually materialises the product. It is the model you reason with when you predict what a query returns. The keyword `INNER` is optional and purely decorative: `FROM a JOIN b ON …` and `FROM a INNER JOIN b ON …` are identical. Many teams write `INNER` explicitly so that the reader never has to wonder whether an `OUTER` was intended and lost. ## Three outcomes, only one survives SQL predicates are three-valued: TRUE, FALSE or UNKNOWN. The join keeps a pair **only** when the predicate is TRUE. A pair whose predicate is FALSE is discarded, and so is a pair whose predicate is UNKNOWN — which is what any comparison against NULL produces. "Not TRUE" is the rejection rule, not "FALSE". ## Worked example ```sql -- customers: (1,'Ann'), (2,'Bob'), (3,'Cara') -- orders: (10, 1), (11, 1), (12, 2), (13, 9) SELECT c.id, c.name, o.id AS order_id FROM customers c INNER JOIN orders o ON o.customer_id = c.id; ``` The result has three rows: `(1,'Ann',10)`, `(1,'Ann',11)`, `(2,'Bob',12)`. Two things vanished. Cara has no orders, so she is not in the output at all — the query can no longer tell you that she exists. Order 13 references customer 9, who is not in `customers`, so that order is gone too. Neither loss produces an error, a warning, or a NULL-filled row. If your report is "orders per customer" and it silently omits customers with zero orders, this is why. ## Symmetry Inner joins are commutative in their row content: `A INNER JOIN B ON p` and `B INNER JOIN A ON p` contain the same pairs. Only the column order of `SELECT *` changes. Inner joins are also associative, so `(A JOIN B) JOIN C` and `A JOIN (B JOIN C)` agree, as long as each ON predicate references tables that are in scope. This is precisely what is **not** true of outer joins, where the preserved side is baked into the operator and reordering can change the answer. Being able to say "inner joins commute, outer joins do not" is a good short answer to the follow-up. ## An inner join is a filter The most useful way to hold this in your head: every inner join you add to a `FROM` clause is simultaneously a lookup **and** a `WHERE` clause. Joining a fact table to a lookup table to fetch a label also asserts "and a matching label must exist". In a chain `a JOIN b JOIN c JOIN d`, a row must find a partner at *every* link to reach the output, so the losses are cumulative. When you actually want "attach the label if there is one, keep the row either way", the inner join is the wrong operator and you want an outer join, which fills the missing side with NULLs instead of deleting the row. ## Row counts are not bounded by either input Dropping rows is only half the story. If several right-hand rows satisfy the predicate for one left-hand row, that left row appears once per match. So an inner join can return fewer rows than either input (few matches), the same number (a strict 1:1 match), or many more (duplicate keys on both sides). Ann above appears twice because she has two orders. Never assume the join preserved your fact-table row count — verify it. ## The ON clause takes any boolean predicate Equality on a foreign key is the common case, but `ON` accepts any boolean expression: ranges, inequalities, several conditions combined with `AND`/`OR`, expressions over columns of either side. The join rule does not change — pairs where the expression is TRUE survive. ## What interviewers listen for A weak answer is "INNER JOIN combines two tables where they match" and nothing more. The strong answer adds the two consequences that cause real bugs: unmatched rows *silently disappear* (so an inner join is a filter), and matching is *many-to-many* (so the output can be larger than the inputs). Almost every wrong result attributed to "the join" traces back to one of those two.
- Does swapping the two tables around the INNER JOIN keyword change the result?Not the set of rows. Inner joins are commutative and associative, so `A INNER JOIN B ON p` and `B INNER JOIN A ON p` return the same pairs; only the column order produced by `SELECT *` differs. That symmetry is exactly what outer joins lack, because an outer join names which side is preserved.
- Is `INNER` required, and what does a bare JOIN mean?`INNER` is optional. A bare `JOIN ... ON` is an inner join in standard SQL and in every mainstream engine. Writing `INNER` explicitly is a readability habit: it tells the next reader that no `LEFT` or `FULL` was intended and accidentally deleted.
- Can an INNER JOIN return more rows than either input table?Yes. The join emits one row per qualifying pair, so a left row with three matches contributes three output rows. With duplicate keys on both sides the counts multiply — two matching rows on the left and three on the right give six. Row count is bounded only by the product of the inputs.
saying these in an interview costs you the question
- Says unmatched rows come back filled with NULLs
- Claims INNER JOIN returns at most one row per left row
- Thinks the result always has as many rows as the left table
- Believes JOIN without INNER means something different
- Says the join keeps pairs where the predicate is not FALSE