SQL JOINs Explained: INNER vs LEFT JOIN and the Row Multiplication Trap

A podcast network asks for a report: for each show, the number of episodes, the total minutes and the number of sponsors. The query you would write first looks perfectly reasonable. It also returns "Night Shift Radio: 6 episodes, 200 minutes", when the show has three episodes totalling 100 minutes, and it silently drops the show with no episodes at all. Both errors come from how joins combine rows.

Why data is split across tables

Putting everything in one wide table repeats data. A show's title would be copied onto every episode and every sponsor row, and renaming a show would mean editing dozens of rows. Relational design instead stores each fact once and links tables with keys: episodes.show_id is a foreign key pointing to shows.show_id. A JOIN is the operation that stitches the pieces back together at query time.

The schema below has one parent table and two child tables. A show has many episodes and many sponsors, but episodes and sponsors are unrelated to each other, which matters at the end of the lesson.

CREATE TABLE shows (
  show_id INTEGER PRIMARY KEY,
  title   TEXT NOT NULL
);

CREATE TABLE episodes (
  episode_id INTEGER PRIMARY KEY,
  show_id    INTEGER NOT NULL REFERENCES shows(show_id),
  title      TEXT    NOT NULL,
  minutes    INTEGER NOT NULL
);

CREATE TABLE sponsors (
  sponsor_id INTEGER PRIMARY KEY,
  show_id    INTEGER NOT NULL REFERENCES shows(show_id),
  name       TEXT    NOT NULL
);

INSERT INTO shows VALUES
  (1, 'Night Shift Radio'), (2, 'Soil and Soul'),
  (3, 'The Long Commute'),  (4, 'Half Past Noon');

INSERT INTO episodes VALUES
  (1, 1, 'The 3am Bakery',     32),
  (2, 1, 'Bridge Inspectors',  41),
  (3, 1, 'Lighthouse Relief',  27),
  (4, 2, 'Compost Wars',       25),
  (5, 2, 'Seeds of Doubt',     38),
  (6, 3, 'Platform 9 Stories', 45);

INSERT INTO sponsors VALUES
  (1, 1, 'Brightline Torches'),
  (2, 1, 'Kettle & Co'),
  (3, 2, 'GreenGrip Gloves'),
  (4, 3, 'Kettle & Co'),
  (5, 3, 'RailSnack'),
  (6, 3, 'Moonpost Mattresses');

INNER JOIN: only rows that match on both sides

INNER JOIN ... ON pairs each episode row with the show row whose key matches, and keeps only pairs where the condition is true. Aliases (s, e) keep the query readable, and prefixing columns with them avoids ambiguity when both tables have a title.

SELECT s.title AS show, e.title AS episode, e.minutes
FROM shows AS s
INNER JOIN episodes AS e ON e.show_id = s.show_id
ORDER BY s.show_id, e.episode_id;

show              | episode            | minutes
------------------+--------------------+--------
Night Shift Radio | The 3am Bakery     |      32
Night Shift Radio | Bridge Inspectors  |      41
Night Shift Radio | Lighthouse Relief  |      27
Soil and Soul     | Compost Wars       |      25
Soil and Soul     | Seeds of Doubt     |      38
The Long Commute  | Platform 9 Stories |      45

Six episodes produce six rows, each carrying its show's title. The show Half Past Noon has no episodes, so no pair exists for it and it is absent. That is the defining behaviour of an inner join: a row without a partner disappears.

LEFT JOIN: keep every row from the left table

LEFT JOIN keeps all rows from the table on the left, and fills the right-hand columns with NULL where there is no match. Use it when the absence of related rows is itself information. Adding WHERE right_key IS NULL turns it into an anti-join, a clean way to ask "which shows have nothing attached?"

SELECT s.title AS show, e.title AS episode
FROM shows AS s
LEFT JOIN episodes AS e ON e.show_id = s.show_id
ORDER BY s.show_id, e.episode_id;

-- anti-join: shows with no episodes at all
SELECT s.title
FROM shows AS s
LEFT JOIN episodes AS e ON e.show_id = s.show_id
WHERE e.episode_id IS NULL;

show              | episode
------------------+-------------------
Night Shift Radio | The 3am Bakery
Night Shift Radio | Bridge Inspectors
Night Shift Radio | Lighthouse Relief
Soil and Soul     | Compost Wars
Soil and Soul     | Seeds of Doubt
The Long Commute  | Platform 9 Stories
Half Past Noon    | NULL

title
--------------
Half Past Noon

-- filter in ON: every show survives
SELECT s.title AS show, e.title AS episode
FROM shows AS s
LEFT JOIN episodes AS e ON e.show_id = s.show_id AND e.minutes > 40
ORDER BY s.show_id, e.episode_id;

-- same filter in WHERE: the outer join collapses into an inner join
SELECT s.title AS show, e.title AS episode
FROM shows AS s
LEFT JOIN episodes AS e ON e.show_id = s.show_id
WHERE e.minutes > 40
ORDER BY s.show_id, e.episode_id;

show              | episode
------------------+-------------------
Night Shift Radio | Bridge Inspectors
Soil and Soul     | NULL
The Long Commute  | Platform 9 Stories
Half Past Noon    | NULL

show              | episode
------------------+-------------------
Night Shift Radio | Bridge Inspectors
The Long Commute  | Platform 9 Stories

The first query keeps all four shows and shows an episode only where it lasts more than 40 minutes. The second applies the same condition after the join, so shows with NULL minutes are eliminated: Soil and Soul and Half Past Noon vanish. Both are legitimate queries, but they answer different questions.

The row-multiplication trap

Start with the safe case: counting episodes and minutes per show with a single left join. The result is correct and includes the show with no episodes, thanks to COALESCE replacing the NULL sum.

SELECT s.title AS show,
       COUNT(e.episode_id)          AS episodes,
       COALESCE(SUM(e.minutes), 0)  AS total_minutes
FROM shows AS s
LEFT JOIN episodes AS e ON e.show_id = s.show_id
GROUP BY s.show_id
ORDER BY s.show_id;

show              | episodes | total_minutes
------------------+----------+--------------
Night Shift Radio |        3 |           100
Soil and Soul     |        2 |            63
The Long Commute  |        1 |            45
Half Past Noon    |        0 |             0

Now add sponsors to the same query by joining a second child table. The report asks for episodes and minutes again, and the result is wrong.

SELECT s.title AS show,
       COUNT(*)       AS episodes,
       SUM(e.minutes) AS total_minutes
FROM shows AS s
JOIN episodes AS e ON e.show_id = s.show_id
JOIN sponsors AS p ON p.show_id = s.show_id
GROUP BY s.show_id
ORDER BY s.show_id;

show              | episodes | total_minutes
------------------+----------+--------------
Night Shift Radio |        6 |           200
Soil and Soul     |        2 |            63
The Long Commute  |        3 |           135

Night Shift Radio shows 6 episodes and 200 minutes (the truth is 3 and 100), The Long Commute shows 3 episodes and 135 minutes (the truth is 1 and 45), and Half Past Noon is gone. The reason is that a join produces one row per combination. A show with 3 episodes and 2 sponsors yields 3 x 2 = 6 rows, so each episode is counted once per sponsor. Soil and Soul happens to look right only because it has exactly one sponsor, which is the sneakiest version of this bug: it passes a quick check on some rows.

There are two sound fixes. You can aggregate each child table separately, in subqueries, and join the already-summarised results, as below. Or, for counts, use COUNT(DISTINCT e.episode_id). Be careful with SUM(DISTINCT e.minutes): it would collapse two episodes of equal length into one and is not a fix at all.

SELECT s.title AS show, ep.total_minutes, sp.sponsors
FROM shows AS s
LEFT JOIN (SELECT show_id, SUM(minutes) AS total_minutes
           FROM episodes GROUP BY show_id) AS ep ON ep.show_id = s.show_id
LEFT JOIN (SELECT show_id, COUNT(*) AS sponsors
           FROM sponsors GROUP BY show_id) AS sp ON sp.show_id = s.show_id
ORDER BY s.show_id;

show              | total_minutes | sponsors
------------------+---------------+---------
Night Shift Radio |           100 |        2
Soil and Soul     |            63 |        1
The Long Commute  |            45 |        3
Half Past Noon    |          NULL |     NULL

Each subquery returns at most one row per show, so no multiplication can occur, and the left joins keep every show. Half Past Noon appears with NULLs; wrap them in COALESCE(..., 0) if the report needs zeros. A related surprise is the accidental cross join: listing two tables with a comma, or joining without a matching ON, pairs every row with every row.

SELECT COUNT(*) AS cross_rows FROM shows, episodes;

cross_rows
----------
        24

Four shows times six episodes gives 24 rows, with no relationship between them. On two tables of a million rows each, that would be a trillion rows, which is why a forgotten join condition can bring a database to its knees.

Common mistakes

• Joining two one-to-many tables, then aggregating. Symptom: sums and counts that are multiples of the right answer. Aggregate each child separately first.
• Right-table filter in WHERE after a LEFT JOIN. Symptom: rows you expected to see with NULLs are missing. Move the filter into ON.
• Forgetting the ON condition. Symptom: result row counts equal the product of the table sizes, and queries are suddenly slow.
• Ambiguous column names. Error: ambiguous column name: title (SQLite). Prefix columns with their alias.
• Joining on non-unique columns by accident, such as a name instead of an id. Duplicate names create extra pairs. Join on keys.

How this shows up in real projects and interviews

Reporting queries that quietly double-count are among the most common data bugs in real teams, usually discovered when finance disagrees with a dashboard. Interviewers like asking "what happens to rows with no match" and "why is my SUM too big". A good habit is to check the row count after each join: if it grew when you did not expect it to, find out why before you aggregate. Joins perform well only when the key columns are indexed, which is the subject of Database Indexing. Later, Advanced SQL shows window functions that avoid some of these self-joins entirely.