SQL Joins: INNER, LEFT, and Why Rows Duplicate or Disappear

Real data is not kept in one wide table. The tool library keeps tools in one table, members in another, and loans in a third, because a member's address should be written down once rather than copied onto every loan they have ever taken out. That design is good for storing data and inconvenient for reading it, and a join is how you read across it.

Two things about joins genuinely surprise people, and both have the same cause. A join can return more rows than the table you started from, and a join can return fewer. Neither is a bug, and by the end of this lesson you should be able to predict which will happen before you press Run.

Every example builds all of its tables, because each run in this playground starts from an empty database.

The shape of the data

Three small tables. loans.member_id and loans.tool_id hold the id of a row in another table — that is all a "foreign key" is, a column whose values point at another table's primary key.

CREATE TABLE members (
  id     INTEGER PRIMARY KEY,
  name   TEXT,
  joined TEXT
);

CREATE TABLE tools (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  shelf TEXT
);

CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER,
  tool_id   INTEGER,
  taken_on  TEXT
);

INSERT INTO members (id, name, joined) VALUES
  (1, 'Asha',  '2025-11-02'),
  (2, 'Bruno', '2026-01-14'),
  (3, 'Cleo',  '2026-02-28'),
  (4, 'Devi',  '2026-03-30');

INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'),
  (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'),
  (13, 'tile cutter', 'power');

INSERT INTO loans (member_id, tool_id, taken_on) VALUES
  (1, 10, '2026-03-04'),
  (1, 11, '2026-03-21'),
  (2, 12, '2026-03-19'),
  (3, 11, '2026-04-02'),
  (1, 12, '2026-04-08');

SELECT count(*) AS members FROM members;
SELECT count(*) AS tools FROM tools;
SELECT count(*) AS loans FROM loans;

members
-------
      4
(1 row)

tools
-----
    4
(1 row)

loans
-----
    5
(1 row)

Four members, four tools, five loans. Devi has never borrowed anything, and the tile cutter has never been borrowed — those two facts are what make the rest of this lesson interesting.

INNER JOIN

JOIN ... ON says which two tables to combine and how a row in one is matched to a row in the other. INNER JOIN is the full name; plain JOIN means the same thing.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (2, '2026-03-19'), (3, '2026-04-02'), (1, '2026-04-08');

SELECT members.name, loans.taken_on
FROM members
JOIN loans ON loans.member_id = members.id
ORDER BY members.name, loans.taken_on;

name  | taken_on
------+-----------
Asha  | 2026-03-04
Asha  | 2026-03-21
Asha  | 2026-04-08
Bruno | 2026-03-19
Cleo  | 2026-04-02
(5 rows)

Read the ON clause as the rule the database applies to every possible pairing: keep the pair where the loan's member_id equals the member's id. Five loans, five rows out.

Writing members.name rather than name is not required when a column name appears in only one table, but it is a good habit — it tells the reader where each column came from, and it becomes required the moment two tables share a column name. Aliases keep it short:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id, taken_on) VALUES
  (1, 10, '2026-03-04'), (1, 11, '2026-03-21'), (2, 12, '2026-03-19'),
  (3, 11, '2026-04-02'), (1, 12, '2026-04-08');

SELECT m.name AS member, t.name AS tool, t.shelf, l.taken_on
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
JOIN tools   AS t ON t.id = l.tool_id
ORDER BY l.taken_on;

member | tool           | shelf  | taken_on
-------+----------------+--------+-----------
Asha   | hedge trimmer  | garden | 2026-03-04
Bruno  | wheelbarrow    | garden | 2026-03-19
Asha   | cordless drill | power  | 2026-03-21
Cleo   | cordless drill | power  | 2026-04-02
Asha   | wheelbarrow    | garden | 2026-04-08
(5 rows)

Both tables have a column called name, so m.name and t.name are the only way to say which one you mean, and AS member and AS tool stop the output having two columns with the same heading. Joining a third table is just another JOIN ... ON line; there is no limit worth worrying about at this scale.

Notice which table this query starts from. FROM loans and then joining outwards is usually the clearest way to write a query about events, because the loan is the thing each output row is really about.

Why rows disappear

An inner join keeps only the pairs that match. Anything with no partner on the other side is silently gone — and "silently" is the problem, because a query that drops rows looks exactly like a query that found none.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (2, '2026-03-19'), (3, '2026-04-02'), (1, '2026-04-08');

SELECT m.name, count(*) AS loans
FROM members AS m
JOIN loans AS l ON l.member_id = m.id
GROUP BY m.name
ORDER BY m.name;

name  | loans
------+------
Asha  |     3
Bruno |     1
Cleo  |     1
(3 rows)

Three rows, from a table of four members. Devi has no loans, so no pair involving Devi matched, so Devi is not in the result at all — not even with a zero. If you hand that table to someone as "loans per member", you have quietly under-reported your membership.

LEFT JOIN

A LEFT JOIN keeps every row from the left-hand table, matched or not. Where there is no match, the right-hand table's columns come back as NULL.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (2, '2026-03-19'), (3, '2026-04-02'), (1, '2026-04-08');

SELECT m.name, l.taken_on
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
ORDER BY m.name, l.taken_on;

name  | taken_on
------+-----------
Asha  | 2026-03-04
Asha  | 2026-03-21
Asha  | 2026-04-08
Bruno | 2026-03-19
Cleo  | 2026-04-02
Devi  | NULL
(6 rows)

Six rows now: the five real loans plus one row for Devi with NULL where a date would go. That NULL was manufactured by the join — there is no NULL anywhere in either table.

Which changes how you count. Combine a LEFT JOIN with count() and the choice of what to count decides whether Devi gets a zero or a one:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (2, '2026-03-19'), (3, '2026-04-02'), (1, '2026-04-08');

SELECT m.name, count(*) AS wrong, count(l.id) AS right_answer
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
GROUP BY m.name
ORDER BY m.name;

name  | wrong | right_answer
------+-------+-------------
Asha  |     3 |            3
Bruno |     1 |            1
Cleo  |     1 |            1
Devi  |     1 |            0
(4 rows)

count(*) counts rows in the group, and Devi's group has one row — the manufactured one — so she gets 1 loan she never took. count(l.id) counts non-NULL ids, and hers is NULL, so she correctly gets 0. With a LEFT JOIN, count a column from the right-hand table, never *. This is the most common wrong number in a beginner's report, and it looks perfectly plausible.

Finding the rows with no match

That manufactured NULL is also a tool. Filter for it and you get exactly the rows that failed to match, which answers questions like "which tools has nobody ever borrowed?"

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12);

SELECT t.name, t.shelf
FROM tools AS t
LEFT JOIN loans AS l ON l.tool_id = t.id
WHERE l.id IS NULL;

name        | shelf
------------+------
tile cutter | power
(1 row)

This pattern — LEFT JOIN then WHERE right.something IS NULL — is worth memorising. It is the standard way to ask "what is in A but not in B", and it works because the only rows where the right-hand id is NULL are the rows the join could not match.

Test the IS NULL against a column that can never really be NULL, such as the right table's primary key. If you test a column that genuinely holds NULLs, you will also catch rows that matched perfectly well and just happened to have nothing in that column.

ON versus WHERE, which matters only for LEFT JOIN

For an inner join, a condition in ON and the same condition in WHERE give the same answer. For a LEFT JOIN they do not, and the difference is easy to see side by side:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (2, '2026-03-19'), (3, '2026-04-02'), (1, '2026-04-08');

SELECT m.name, l.taken_on
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id AND l.taken_on >= '2026-04-01'
ORDER BY m.name;

SELECT m.name, l.taken_on
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
WHERE l.taken_on >= '2026-04-01'
ORDER BY m.name;

name  | taken_on
------+-----------
Asha  | 2026-04-08
Bruno | NULL
Cleo  | 2026-04-02
Devi  | NULL
(4 rows)

name | taken_on
-----+-----------
Asha | 2026-04-08
Cleo | 2026-04-02
(2 rows)

The first query asks "every member, and their April loans if any" — four members, with NULL for the two who borrowed nothing in April. The second asks "every member and their loans, then throw away rows that are not April", and since a NULL date is not >= anything, it throws away the manufactured rows too and leaves only real April loans. Putting the extra condition in WHERE turned the LEFT JOIN back into an inner join.

Rule of thumb: conditions about how rows pair up belong in ON; conditions about which result rows you want belong in WHERE. With a LEFT JOIN, any WHERE condition on a right-hand column other than IS NULL is worth a second look.

Why rows duplicate

A join does not "add columns to a row". It builds every pair that satisfies ON, so a left row with three matches becomes three output rows.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, deposit_held INTEGER);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name, deposit_held) VALUES (1, 'Asha', 2000), (2, 'Bruno', 500);
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (1, '2026-04-08'), (2, '2026-03-19');

SELECT m.name, m.deposit_held, l.taken_on
FROM members AS m
JOIN loans AS l ON l.member_id = m.id
ORDER BY m.name, l.taken_on;

SELECT sum(m.deposit_held) AS total_deposit_held
FROM members AS m
JOIN loans AS l ON l.member_id = m.id;

name  | deposit_held | taken_on
------+--------------+-----------
Asha  |         2000 | 2026-03-04
Asha  |         2000 | 2026-03-21
Asha  |         2000 | 2026-04-08
Bruno |          500 | 2026-03-19
(4 rows)

total_deposit_held
------------------
              6500
(1 row)

Asha's 2000 appears on three rows, because she has three loans. The first result makes that obvious. The second result is the same fact hiding: the total deposit held by two members is £25, not £65, but summing a column from the left table after a one-to-many join counts it once per match. This is called fan-out, and it is the reason a report suddenly triples after someone adds an innocent extra join.

Two ways out. Aggregate the many-side first and join to the summary — that is lesson 6 — or, when you only need the total, sum a distinct set:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, deposit_held INTEGER);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT);
INSERT INTO members (id, name, deposit_held) VALUES (1, 'Asha', 2000), (2, 'Bruno', 500);
INSERT INTO loans (member_id, taken_on) VALUES
  (1, '2026-03-04'), (1, '2026-03-21'), (1, '2026-04-08'), (2, '2026-03-19');

SELECT
  sum(m.deposit_held)  AS inflated,
  count(*)             AS joined_rows,
  count(DISTINCT m.id) AS members_involved
FROM members AS m
JOIN loans AS l ON l.member_id = m.id;

inflated | joined_rows | members_involved
---------+-------------+-----------------
    6500 |           4 |                2
(1 row)

count(DISTINCT m.id) is honest where count(*) is not. Whenever a joined result has a count that looks too big by a suspiciously round factor, count the distinct key and compare.

A worked example

Members, their loan counts, and the shelves they borrow from — with nobody left out:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, joined TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER, taken_on TEXT);

INSERT INTO members (id, name, joined) VALUES
  (1, 'Asha', '2025-11-02'), (2, 'Bruno', '2026-01-14'),
  (3, 'Cleo', '2026-02-28'), (4, 'Devi', '2026-03-30');
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id, taken_on) VALUES
  (1, 10, '2026-03-04'), (1, 11, '2026-03-21'), (2, 12, '2026-03-19'),
  (3, 11, '2026-04-02'), (1, 12, '2026-04-08');

SELECT
  m.name                            AS member,
  count(l.id)                       AS loans,
  count(DISTINCT t.shelf)           AS shelves_used,
  COALESCE(group_concat(DISTINCT t.shelf), 'none') AS shelves
FROM members AS m
LEFT JOIN loans AS l ON l.member_id = m.id
LEFT JOIN tools AS t ON t.id = l.tool_id
GROUP BY m.id, m.name
ORDER BY loans DESC, m.name;

member | loans | shelves_used | shelves
-------+-------+--------------+-------------
Asha   |     3 |            2 | garden,power
Bruno  |     1 |            1 | garden
Cleo   |     1 |            1 | power
Devi   |     0 |            0 | none
(4 rows)

Every decision in that query is one of the traps above. LEFT JOIN twice, so Devi survives to the output. count(l.id) rather than count(*), so Devi's loan count is 0 and not 1. count(DISTINCT t.shelf), because Asha borrowed two garden tools and a plain count would say three shelves. COALESCE(..., 'none'), because group_concat over nothing but NULL is NULL. And GROUP BY m.id, m.name rather than just the name, so two members who happened to share a first name would still be counted separately.

Common mistakes

Leaving the ON clause off

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO tools (id, name) VALUES (10, 'hedge trimmer'), (11, 'cordless drill');

SELECT m.name AS member, t.name AS tool
FROM members AS m, tools AS t
ORDER BY m.name, t.name;

member | tool
-------+---------------
Asha   | cordless drill
Asha   | hedge trimmer
Bruno  | cordless drill
Bruno  | hedge trimmer
Cleo   | cordless drill
Cleo   | hedge trimmer
(6 rows)

Three members and two tools gave six rows: every member paired with every tool. That is a cross join, and writing two tables separated by a comma with no matching condition is how you get one by accident. It is occasionally what you want — a calendar crossed with a room list, say — in which case write CROSS JOIN so the reader knows it was deliberate. If you meant a real join, the missing ON shows up as a row count that is the product of the two tables rather than something close to one of them.

Joining on the wrong pair of columns

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (2, 11), (3, 11);

SELECT m.name, l.tool_id
FROM members AS m
JOIN loans AS l ON l.id = m.id
ORDER BY m.name;

name  | tool_id
------+--------
Asha  |      10
Bruno |      11
Cleo  |      11
(3 rows)

l.id = m.id matches a loan's own id against a member's id. Both columns are integers, both tables have rows with ids 1, 2 and 3, so the query runs and returns a plausible-looking three rows of complete nonsense. No database can catch this for you; the only defence is reading the ON clause back and asking whether those two columns really mean the same kind of thing. Declaring a real FOREIGN KEY at least documents which pairing was intended, which lesson 7 covers.

Expecting RIGHT JOIN to be available everywhere

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno');
INSERT INTO loans (member_id) VALUES (1), (1), (99);

SELECT m.name, l.id AS loan_id
FROM members AS m
RIGHT JOIN loans AS l ON l.member_id = m.id
ORDER BY l.id;

name | loan_id
-----+--------
Asha |       1
Asha |       2
NULL |       3
(3 rows)

RIGHT JOIN keeps every row of the right-hand table instead of the left, so the orphan loan pointing at member 99 survives with a NULL name. It works here because this playground runs SQLite 3.49, and it is worth knowing that SQLite only gained RIGHT JOIN and FULL OUTER JOIN in version 3.39 — plenty of software still ships an older build. Every RIGHT JOIN can be rewritten as a LEFT JOIN with the two tables swapped, which is both portable and, most people find, easier to read.

Next steps

You can now read across tables, keep the rows with no match, and spot a duplicated or missing row before it reaches a report. The next step is queries built out of other queries: the subqueries and CTEs lesson shows how to aggregate first and join to the result, which is the clean fix for the fan-out problem above. Take the worked example into the SQL playground and change one LEFT JOIN to JOIN to watch Devi vanish.