Joining Techniques

Joins combine data from multiple tables. This is essential because real databases store related information in separate tables to avoid redundancy.

Reading the ERD: cardinality

Before joining two tables, read how their rows relate. On an entity relationship diagram (ERD), each end of a line says how many rows of that table one row of the other table can have. Two bars mean exactly one, a circle means zero is allowed, and the fan (a crow’s foot) means many. Scroll through three real relationships in this database.

One caution about this data: the Washington rows of pnw_towns are incomplete, so a few Washington counties show no towns or no county seat because rows are missing, not because the county has none. The diagrams point these out where they appear.

pnw_towns.primary_county_id  →  pnw_counties.county_id

pnw_counties.county_seat_town_id  →  pnw_towns.town_id

a town can lie in up to three counties; a county holds many towns

INNER JOIN

An INNER JOIN returns only rows where there’s a match in BOTH tables. Think of it as finding the intersection.

SELECT * FROM left_table AS l INNER JOIN right_table AS r ON l.id = r.id;

Joining 1: Enrich town data with county information

Scenario: You want to see each town alongside when its county was established and the county’s etymology. This requires combining data from both tables. Show town, population_2020_census, county, year_established, and etymology (state isn’t selected here). Sort by population_2020_census, largest first.

Notice the join condition: pnw_towns.primary_county_id is a foreign key pointing at pnw_counties.county_id, so one equality is all it takes. Joining on names instead would need two conditions (t.primary_county = c.county AND t.state = c.state) — leave out the state and every town in Washington County, Oregon happily matches the state of Washington’s counties too. ID joins sidestep that trap entirely, which is why real warehouses key everything this way.

Joining 2: Find which towns are county seats

Scenario: County seats often have special administrative importance. Join to identify them. Show each seat’s town and state, its population as town_pop, its county, and the county’s population as county_pop. Sort by town_pop, largest first.

Joining 3: Compare town population to county population

Scenario: What percentage of each county’s population lives in each town? Show town, its population as town_pop, the county, its population as county_pop, and the percentage (1 decimal) as pct_of_county. Sort by pct_of_county, highest first.

LEFT JOIN

A LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table. Non-matches get NULL values.

SELECT * FROM left_table AS l LEFT JOIN right_table AS r ON l.id = r.id;

One thing to watch: if the right table has more than one row for a key, a matched left row is repeated once per match, so the join can return more rows than the left table started with.

-- right_dup has key 1 twice
SELECT * FROM left_table AS l LEFT JOIN right_dup AS r ON l.id = r.id;

Joining 4: List all counties with their county seat populations (if available)

Scenario: Some county seats might not be in our towns database. LEFT JOIN ensures all counties appear. Show county, state, county_seat, and the seat’s population as seat_population. List counties with no seat_population first, then sort alphabetically by county.

Joining 5: Flag whether each town is a county seat

Scenario: Add a column indicating county seat status for every town. Show town, state, population_2020_census, an is_county_seat flag of ‘Yes’ or ‘No’, and the county served as seat_of_county. List county seats first, then sort by population_2020_census, largest first.

Anti-Join

An anti-join finds rows in one table that have NO match in another table. It’s implemented as a LEFT JOIN with a WHERE clause checking for NULLs.

SELECT l.* FROM left_table AS l
 LEFT JOIN right_table AS r ON l.id = r.id
 WHERE r.id IS NULL;

Joining 6: Find county seats not in our towns database

Scenario: Data quality check - which county seats are missing from our towns table? List each affected county with its state and county_seat. Sort by state, then alphabetically by county.

Joining 7: Find large towns that are NOT county seats

Scenario: Identify towns with more than 25,000 people that are not county seats. Show town, state, population_2020_census, and primary_county. Sort by population_2020_census, largest first.

Joining 8: Find counties with no towns in our database

Scenario: Which counties have zero incorporated towns listed? This might indicate missing data. Show each such county with its state, population_2022, and county_seat. Sort by population_2022, largest first.

Order of operations: putting it together

A query with a join, a filter, groups, a sort, and a LIMIT does not run in the order it is written. The first diagram lines the two orders up; the second runs one real query through all eight clauses, from FROM to LIMIT. (The single-table version is on Sorting and Grouping.)

SELECT …
  FROM … JOIN … ON …
 WHERE …
 GROUP BY …
HAVING …
 ORDER BY …
 LIMIT …;

SELECT c.county, COUNT(*) AS n_towns,
       SUM(t.population_2020_census) AS pop
  FROM pnw_towns AS t
  JOIN pnw_counties AS c
    ON c.county_id = t.primary_county_id
 WHERE c.state = 'Oregon'
   AND c.county IN ('Crook', 'Harney', 'Jefferson')
 GROUP BY c.county
HAVING COUNT(*) >= 2
 ORDER BY pop DESC
 LIMIT 1;

Going further

Two more join examples combine a join with GROUP BY: counting towns per county (including the counties with none) and a full county dashboard. They are in Going Further.