All topics
4 / 66
queries & joins

Join Types

Combine rows from multiple tables — INNER for matches only, LEFT for matches plus unmatched-left, FULL for both sides.

A normalised database splits related facts across tables — the customer here, their orders there. A join puts them back together.

INNER = both lists agree. LEFT = "everyone on the invitee list, with their RSVP if any." FULL = "everyone on either list."

Key Concepts

1
Choosing a join type is really one decision: what should happen to rows with no match on the other side?
2
The four answers. Take customers and their orders:
3
    customers           orders
    1  Asha             10  cust 1
    2  Ravi             11  cust 1
    3  Meera            12  cust 2
                        13  cust 9   <- deleted customer
4
  - `INNER JOIN` — only matched rows. Asha twice, Ravi once. Meera and order 13 disappear.
  - `LEFT JOIN` — every customer. Asha twice, Ravi once, **Meera once with NULL order**.
  - `RIGHT JOIN` — every order, including order 13 with a NULL customer.
  - `FULL OUTER JOIN` — both unmatched sides: Meera and order 13.
INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOIN
5
CROSS JOIN matches nothing at all and returns every combination — 3 customers x 4 orders = 12 rows. Sometimes deliberate, more often a missing join condition.
CROSS JOIN
6
The bug interviewers reach for first. This looks like a LEFT JOIN and is not one:
LEFT JOIN
7
    SELECT c.name, o.total
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.status = 'PAID'
8
Meera has no order, so her o.status is NULL. NULL = 'PAID' is not true, so the WHERE throws her row away — the exact row the LEFT JOIN existed to keep.
o.statusNULL = 'PAID'WHERELEFT JOIN
9
The fix is where the condition goes:
10
    LEFT JOIN orders o
      ON o.customer_id = c.id
     AND o.status = 'PAID'
11
In the ON clause the condition decides what counts as a match. In the WHERE clause it filters after the join, and NULL loses. The rule: conditions on the outer table's columns belong in ON.
ONWHERE
12
Fan-out quietly inflates your totals. Join a customer to their 3 orders and the customer's row is repeated 3 times. Now SUM(c.credit_limit) counts that limit three times.
SUM(c.credit_limit)
13
The count is not wrong, the query is: you are summing over a multiplied result. Aggregate the orders first in a subquery, then join one row to one row.
14
What the database actually does. EXPLAIN shows a physical strategy, not the join type you typed: nested loops when one side is tiny or indexed, a hash join for large unindexed equality joins, a merge join when both inputs are already sorted. A slow join is usually nested loops over far more rows than the optimiser expected.
EXPLAIN
15
What the interviewer is probing.1. "What is the difference between an inner and a left join?" Probing: the basics. Stalls: "One is bigger." Moves up: inner keeps only matching rows; left keeps every left row with nulls where there is no match.
16
2. "Why does a left join with a WHERE on the right table behave like an inner join?" Probing: the classic trap. Stalls: "It does not." Moves up: the WHERE filters out the null rows the join just produced — the condition belongs in the ON clause.
17
3. "What join algorithms might the engine choose?" Probing: the physical layer. Stalls: "Just joins." Moves up: nested loop for small inputs with an index, hash join for large unsorted sets, merge join when both are sorted.
18
4. "Why can a join multiply rows unexpectedly?" Probing: cardinality. Stalls: "It cannot." Moves up: a one-to-many join repeats the left row per match, which silently inflates aggregates unless you aggregate first.
Use cases:JoinsNULLCartesian