Joining tables

Why join

Lesson 1 split Larkspur County into three tables so each fact is stored once: a town's name lives in towns, and a leader's row just says town_id = 5. That's good for storing, and bad for reading. Lesson 4's answers all said "town 5", not "Dunmore". A join puts the tables back together for one query, matching rows through the key that links them. Nothing is copied or changed: the tables stay as they are, and the answer is built fresh each time.

Think of a coat check. You hand over your coat and get a ticket with a number. The coat goes on a rail, the ticket goes in your pocket, and neither knows anything about the other except that number. A join is the attendant matching tickets to coats. It only works because every number is unique: match on "a blue coat" instead, and you'll go home with someone else's.

JOIN ... ON

The administrators, with their towns' names:

SELECT towns.name, leaders.name, leaders.role
FROM leaders JOIN towns ON towns.id = leaders.town_id
WHERE leaders.role = 'administrator';

     name      |    name    |     role      
---------------+------------+---------------
 Ashford       | Priya Shah | administrator
 Brennan Falls | Leo Park   | administrator
 Fairview      | Nora Quinn | administrator
 Kestrel Bay   | Ken Moss   | administrator
(4 rows)

JOIN towns ON towns.id = leaders.town_id says: for each leader, find the town whose id equals the leader's town_id, and put the two rows side by side. That condition is the foreign key from Lesson 1, written out. JOIN on its own means an inner join: only pairs that match make it into the answer.

Both tables have a column called name, so every column says which table it's from: towns.name, leaders.name. Leave that out, and Postgres won't guess:

SELECT name, role FROM leaders JOIN towns ON towns.id = leaders.town_id;

ERROR:  column reference "name" is ambiguous
LINE 1: SELECT name, role FROM leaders JOIN towns ON towns.id = lead...
               ^

role would have been fine, since only leaders has one. But the answer's two name columns are confusing anyway. Aliases fix both: a short name for each table after FROM and JOIN, and AS to name the columns in the answer. Lesson 3's current mayors, with names this time:

SELECT t.name AS town, l.name AS leader
FROM leaders l JOIN towns t ON t.id = l.town_id
WHERE l.role = 'mayor' AND l.ended IS NULL
ORDER BY t.id;

     town      |     leader     
---------------+----------------
 Ashford       | Thomas Reyes
 Ashford       | Thomas Reyes
 Brennan Falls | Dana Ruiz
 Cedar Hollow  | Sam Whitfield 
 Dunmore       | Grace Lin
 Dunmore       | Owen Price
 Elk Ridge     | Ivy Chen
 Fairview      | Marcus Bell
 Harlow        | Rosa Diaz
 Kestrel Bay   | Ana Torres
 Linden        | Joy Hart
(11 rows)

leaders l means "call leaders just l in this query". Short aliases are the norm once there's more than one table, and the rest of this lesson uses them.

Lesson 4's meetings per town, with names:

SELECT t.name, count(*) AS meetings
FROM meetings m JOIN towns t ON t.id = m.town_id
GROUP BY t.id, t.name ORDER BY t.id;

     name      | meetings 
---------------+----------
 Ashford       |        4
 Ashford       |        2
 Brennan Falls |        4
 Cedar Hollow  |        2
 Dunmore       |        3
 Elk Ridge     |        1
 Fairview      |        4
 Glen Oak      |        2
 Harlow        |        1
 Kestrel Bay   |        4
 Linden        |        3
(11 rows)

Notice GROUP BY t.id, t.name, not just t.name. Group by name, and the two Ashfords become one group of 6 meetings. Group by the key, and they stay apart. Adding t.name costs nothing, since each id has one name, and it lets SELECT show the name.

Still eleven rows, though. Juniper is still missing. That comes after two ways joins go wrong.

Forgetting the condition

Older SQL lists the tables with a comma, and puts the condition in WHERE. Forget the condition, and nothing complains:

SELECT count(*) FROM towns, leaders;

 count 
-------
   240
(1 row)

12 towns times 20 leaders: every town paired with every leader, whether they belong together or not. That's a cross join, and you can ask for one on purpose:

SELECT count(*) FROM towns CROSS JOIN leaders;

 count 
-------
   240
(1 row)

On purpose, it's occasionally useful: every town paired with every month, to fill in a calendar. By accident, it's a classic. Two tables of 10,000 rows each make 100 million. Write joins as JOIN ... ON: then a missing ON is an error, not a giant answer.

Joining on the wrong column

Thomas Reyes is a current mayor twice. Joined on the key, it's clear why:

SELECT t.id, t.name AS town, t.kind, l.name AS leader
FROM leaders l JOIN towns t ON t.id = l.town_id
WHERE l.name = 'Thomas Reyes';

 id |  town   |  kind   |    leader    
----+---------+---------+--------------
  1 | Ashford | city    | Thomas Reyes
  2 | Ashford | village | Thomas Reyes
(2 rows)

Two Ashfords, a mayor of each. Same for meetings: four belong to the city, two to the village.

SELECT t.id, t.name AS town, t.kind, m.held_on
FROM meetings m JOIN towns t ON t.id = m.town_id
WHERE t.name = 'Ashford' ORDER BY m.held_on;

 id |  town   |  kind   |  held_on   
----+---------+---------+------------
  1 | Ashford | city    | 2026-01-12
  2 | Ashford | village | 2026-01-20
  1 | Ashford | city    | 2026-02-09
  1 | Ashford | city    | 2026-02-23
  1 | Ashford | city    | 2026-03-09
  2 | Ashford | village | 2026-03-17
(6 rows)

Now suppose some other table only had the town's name, not its id: a spreadsheet someone imported, say. Joining on the name looks reasonable. Here's what it does, joining each meeting's town to every town with the same name:

SELECT t2.id, t2.kind, m.held_on
FROM meetings m
JOIN towns t1 ON t1.id = m.town_id
JOIN towns t2 ON t2.name = t1.name
WHERE t1.name = 'Ashford' ORDER BY m.held_on, t2.id;

 id |  kind   |  held_on   
----+---------+------------
  1 | city    | 2026-01-12
  2 | village | 2026-01-12
  1 | city    | 2026-01-20
  2 | village | 2026-01-20
  1 | city    | 2026-02-09
  2 | village | 2026-02-09
  1 | city    | 2026-02-23
  2 | village | 2026-02-23
  1 | city    | 2026-03-09
  2 | village | 2026-03-09
  1 | city    | 2026-03-17
  2 | village | 2026-03-17
(12 rows)

Six meetings became twelve rows, and every meeting now belongs to both Ashfords. The village held a meeting on January 12th it never held; the city gets the village's March meeting. No error, and the numbers look plausible. Count meetings per town from this, and both Ashfords get 6.

This is the most expensive mistake in this lesson, and the one CivicGrid's rules are built around: city name plus state is not unique, so every write is matched by a stable key, id or the Census place_fips, never by name. A name is a label for people. A key is for joins.

LEFT JOIN: keep the rows with no match

An inner join drops anything without a partner. Juniper has no meetings, so it had nothing to join to, and vanished. A LEFT JOIN keeps every row from the table on the left, the one after FROM, matched or not. Where there's no match, the right side's columns are NULL:

SELECT t.id, t.name, m.id AS meeting_id
FROM towns t LEFT JOIN meetings m ON m.town_id = t.id
WHERE t.id IN (9, 10);

 id |  name   | meeting_id 
----+---------+------------
  9 | Harlow  |         23
 10 | Juniper |           
(2 rows)

Juniper is here at last, with an empty meeting. Now count meetings for every town, two ways:

SELECT t.name, count(*) AS wrong, count(m.id) AS meetings
FROM towns t LEFT JOIN meetings m ON m.town_id = t.id
GROUP BY t.id, t.name ORDER BY t.id;

     name      | wrong | meetings 
---------------+-------+----------
 Ashford       |     4 |        4
 Ashford       |     2 |        2
 Brennan Falls |     4 |        4
 Cedar Hollow  |     2 |        2
 Dunmore       |     3 |        3
 Elk Ridge     |     1 |        1
 Fairview      |     4 |        4
 Glen Oak      |     2 |        2
 Harlow        |     1 |        1
 Juniper       |     1 |        0
 Kestrel Bay   |     4 |        4
 Linden        |     3 |        3
(12 rows)

Twelve towns, and Juniper's count(*) says 1. It's counting rows, and the LEFT JOIN gave Juniper one row, the one with the empty meeting. count(m.id) counts meetings, skips the NULL, and says 0. Lesson 4's difference between count(*) and count(column), mattering for real. After a LEFT JOIN, count a column from the right-hand table.

The empty match is also how you find what's missing. Towns where no meeting matched:

SELECT t.id, t.name
FROM towns t LEFT JOIN meetings m ON m.town_id = t.id
WHERE m.id IS NULL;

 id |  name   
----+---------
 10 | Juniper
(1 row)

That shape, LEFT JOIN, then WHERE the right side IS NULL, is called an anti-join: "rows here with nothing there". It's how you find cities with no mayor, orders with no payment, users who never logged in.

ON or WHERE: where a condition goes

Which towns have no current mayor? Here's the natural first try: LEFT JOIN to keep every town, and filter to current mayors.

SELECT t.id, t.name, l.name AS mayor
FROM towns t LEFT JOIN leaders l ON l.town_id = t.id
WHERE l.role = 'mayor' AND l.ended IS NULL
ORDER BY t.id;

 id |     name      |     mayor      
----+---------------+----------------
  1 | Ashford       | Thomas Reyes
  2 | Ashford       | Thomas Reyes
  3 | Brennan Falls | Dana Ruiz
  4 | Cedar Hollow  | Sam Whitfield 
  5 | Dunmore       | Grace Lin
  5 | Dunmore       | Owen Price
  6 | Elk Ridge     | Ivy Chen
  7 | Fairview      | Marcus Bell
  9 | Harlow        | Rosa Diaz
 11 | Kestrel Bay   | Ana Torres
 12 | Linden        | Joy Hart
(11 rows)

Glen Oak and Juniper are gone, exactly the towns the question was about. Follow the order of work: the LEFT JOIN keeps Juniper with an empty leader, then WHERE l.role = 'mayor' runs. For Juniper, l.role is NULL, so the condition is unknown, not true, and the row is dropped. Glen Oak's only mayor has an ended date, so it goes the same way. A condition on the right-hand table in WHERE turns a LEFT JOIN back into an inner join, silently.

Move the conditions into ON, so they decide which leaders match, not which rows survive:

SELECT t.id, t.name, l.name AS mayor
FROM towns t LEFT JOIN leaders l
  ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
ORDER BY t.id;

 id |     name      |     mayor      
----+---------------+----------------
  1 | Ashford       | Thomas Reyes
  2 | Ashford       | Thomas Reyes
  3 | Brennan Falls | Dana Ruiz
  4 | Cedar Hollow  | Sam Whitfield 
  5 | Dunmore       | Grace Lin
  5 | Dunmore       | Owen Price
  6 | Elk Ridge     | Ivy Chen
  7 | Fairview      | Marcus Bell
  8 | Glen Oak      | 
  9 | Harlow        | Rosa Diaz
 10 | Juniper       | 
 11 | Kestrel Bay   | Ana Torres
 12 | Linden        | Joy Hart
(13 rows)

Every town, and the two with no current mayor show an empty one. Add the anti-join, and that's the answer:

SELECT t.id, t.name
FROM towns t LEFT JOIN leaders l
  ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
WHERE l.id IS NULL;

 id |   name   
----+----------
  8 | Glen Oak
 10 | Juniper
(2 rows)

The rule: conditions about which right-hand rows count go in ON. Conditions about which final rows you want go in WHERE. With an inner join, it makes no difference. With a LEFT JOIN, it's the whole question.

The same question can also be asked with NOT EXISTS, which reads closer to English: towns where there doesn't exist a current mayor.

SELECT t.id, t.name FROM towns t
WHERE NOT EXISTS (
  SELECT 1 FROM leaders l
  WHERE l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
);

 id |   name   
----+----------
  8 | Glen Oak
 10 | Juniper
(2 rows)

The query inside the brackets is a subquery, run for each town. SELECT 1 because it doesn't matter what it returns, only whether it returns anything. Both forms give the same answer here; use whichever you find easier to read.

Three tables, and counts that multiply

Each town, its current mayors, and its meetings, all in one join:

SELECT t.name, count(*) AS rows,
       count(DISTINCT l.id) AS mayors, count(DISTINCT m.id) AS meetings
FROM towns t
JOIN leaders l ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
JOIN meetings m ON m.town_id = t.id
WHERE t.id IN (1, 5)
GROUP BY t.id, t.name ORDER BY t.id;

  name   | rows | mayors | meetings 
---------+------+--------+----------
 Ashford |    4 |      1 |        4
 Dunmore |    6 |      2 |        3
(2 rows)

Ashford's numbers look right. Dunmore has 3 meetings, and 6 rows. Here they are:

SELECT t.name, l.name AS mayor, m.held_on
FROM towns t
JOIN leaders l ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
JOIN meetings m ON m.town_id = t.id
WHERE t.id = 5 ORDER BY m.held_on, l.name;

  name   |   mayor    |  held_on   
---------+------------+------------
 Dunmore | Grace Lin  | 2026-01-08
 Dunmore | Owen Price | 2026-01-08
 Dunmore | Grace Lin  | 2026-02-12
 Dunmore | Owen Price | 2026-02-12
 Dunmore | Grace Lin  | 2026-02-26
 Dunmore | Owen Price | 2026-02-26
(6 rows)

Mayors and meetings have nothing to do with each other, except the town. So the join pairs every mayor with every meeting: 2 times 3 is 6. It's a small cross join, inside each town. Count rows, or sum attendance, and Dunmore's numbers double. Worse, they only double where a town happens to have two mayors, so the rest of the report looks fine.

count(DISTINCT ...) rescues counts, but not sums. The safe way is not to join two unrelated lists to the same table at all: work out one of them separately. Here, the meeting count comes from a subquery, one per town, so it can't multiply:

SELECT t.name AS town,
       string_agg(DISTINCT l.name, ', ') AS current_mayor,
       (SELECT count(*) FROM meetings m WHERE m.town_id = t.id) AS meetings
FROM towns t
LEFT JOIN leaders l ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL
GROUP BY t.id, t.name ORDER BY t.id;

     town      |     current_mayor     | meetings 
---------------+-----------------------+----------
 Ashford       | Thomas Reyes          |        4
 Ashford       | Thomas Reyes          |        2
 Brennan Falls | Dana Ruiz             |        4
 Cedar Hollow  | Sam Whitfield         |        2
 Dunmore       | Grace Lin, Owen Price |        3
 Elk Ridge     | Ivy Chen              |        1
 Fairview      | Marcus Bell           |        4
 Glen Oak      |                       |        2
 Harlow        | Rosa Diaz             |        1
 Juniper       |                       |        0
 Kestrel Bay   | Ana Torres            |        4
 Linden        | Joy Hart              |        3
(12 rows)

One row per town, all twelve. Dunmore's two mayors on one line, its 3 meetings counted once, and Glen Oak and Juniper with no mayor. Everything Lessons 3 and 4 found, in one report a person can read. (Lesson 3's ended < started check is the one thing it doesn't show: Carl Webb's term in Linden is filtered out by ended IS NULL.)

Spot the problem

Work out what's wrong before you read the explanation.

The attendance report. Someone wants total meeting attendance per town, with the current mayor's name on each row, and writes: SELECT t.name, l.name, sum(m.attendance) FROM towns t JOIN leaders l ON l.town_id = t.id AND l.role = 'mayor' AND l.ended IS NULL JOIN meetings m ON m.town_id = t.id GROUP BY t.name, l.name; It runs, and the numbers look reasonable. What's wrong? Two things. The two Ashfords share a name, and their mayors share a name too, so grouping by t.name, l.name merges them into one row: 166, the city's 146 and the village's 20 added together, with nothing to say it's two towns. And Dunmore, with two current mayors, gets one row for each, each showing the town's full attendance, so a reader adding the column counts Dunmore twice. Group by t.id, and get the sums in a subquery, the way the report above gets its meeting count.

Where you are now

You can put linked tables back together, keep the rows with no match, find what's missing, and spot the joins that multiply or cross wires. Lessons 1 to 5 have been reading a database someone else designed, and found its mistakes. Lesson 6 designs one: keys, types and constraints that stop those mistakes getting in at all.

Check yourself

Key terms

• Join: matching rows from two tables through the key that links them, for one query.
• Inner join: JOIN. Only rows with a match on both sides.
• Left join: LEFT JOIN. Every row from the left table, with NULLs where nothing matched.
• Alias: a short name for a table in a query, like towns t.
• Cross join: every row of one table paired with every row of another.
• Anti-join: rows with nothing matching, from LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS.
• Subquery: a query inside another, in brackets.
• Row multiplication: two lists joined to the same table pair up, multiplying the rows and anything summed over them.

Go deeper

• The PostgreSQL tutorial chapter Joins Between Tables (opens in a new tab).
• Try: each meeting with no minutes link, with its town's name and website. (Eight rows. Two of them belong to a town with no website at all: which?)
• Try: towns that have an administrator but no current mayor. (None. Then try towns with a current mayor but no administrator.)