SQL Tutorial

Joining Techniques

📚 Schema reference · towns & counties 3 tables
fips
  • fips
  • name
  • state
pnw_counties
  • county_id
  • county
  • state
  • fips_short
  • county_seat
  • county_seat_town_id
  • year_established
  • origin
  • etymology
  • population_2022
  • land_area_sq_mi
pnw_towns
  • town_id
  • town
  • state
  • primary_county
  • secondary_county
  • tertiary_county
  • primary_county_id
  • secondary_county_id
  • tertiary_county_id
  • population_2020_census
  • population_2010_census
  • land_area_sq_mi

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

INNER JOIN

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

INNER JOIN: keep only matched rows

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

Scroll to build the diagram, one step at a time.

left_tableidval1L12L23L34L4right_tableidval1R14R25R36R4INNER JOIN resultidL.valR.val1L1R14L4R2
Step 1 · the tables Two tables sharing an id key. Each key value has its own color so you can trace it. Keys 1 and 4 live in both; 2 and 3 are left-only; 5 and 6 are right-only.
Step 2 · the scan An inner join walks the id column on both sides and asks one question of every value: does this key appear on the other side?
Step 3 · first match Key 1 is in both tables, so the rows join and the first result row appears, carrying val from each side.
Step 4 · second match Key 4 matches too. The arrow crosses rows: position does not matter, only the key does.
Step 5 · left rows with no match Keys 2 and 3 look for a match on the right and find none (the open circles). An inner join drops them.
Step 6 · right rows with no match Keys 5 and 6 exist only on the right, so an inner join drops them as well. Unmatched on either side is gone.
Step 7 · the result Two tables in, two matched rows out. An inner join is the intersection: only keys on both sides survive.
Text version of this diagram
  1. Step 1 · the tables. Two tables sharing an id key. Each key value has its own color so you can trace it. Keys 1 and 4 live in both; 2 and 3 are left-only; 5 and 6 are right-only.
  2. Step 2 · the scan. An inner join walks the id column on both sides and asks one question of every value: does this key appear on the other side?
  3. Step 3 · first match. Key 1 is in both tables, so the rows join and the first result row appears, carrying val from each side.
  4. Step 4 · second match. Key 4 matches too. The arrow crosses rows: position does not matter, only the key does.
  5. Step 5 · left rows with no match. Keys 2 and 3 look for a match on the right and find none (the open circles). An inner join drops them.
  6. Step 6 · right rows with no match. Keys 5 and 6 exist only on the right, so an inner join drops them as well. Unmatched on either side is gone.
  7. Step 7 · the result. Two tables in, two matched rows out. An inner join is the intersection: only keys on both sides survive.

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).

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.

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.

LEFT JOIN

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

LEFT JOIN: keep every left row

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

Scroll to build the diagram, one step at a time.

left_tableidval1L12L23L34L4right_tableidval1R14R25R36R4LEFT JOIN resultidL.valR.val1L1R12L2NULL3L3NULL4L4R2
Step 1 · the rule Same two tables, new rule: keep every left row, matched or not. The left table is the one we protect.
Step 2 · the matches Keys 1 and 4 match as before, and their result rows come across complete with val from both sides.
Step 3 · no match, but kept Keys 2 and 3 find nothing on the right (open circles), but a left join keeps them anyway and fills the right columns with NULL (the dark cells).
Step 4 · right rows with no match Keys 5 and 6 exist only on the right. A left join does not keep extra right rows, so they are dropped.
Step 5 · the result Four rows out, one per left row, two of them carrying NULL. A left join never loses a row from the left table, but it can hand you missing values to reckon with.
Text version of this diagram
  1. Step 1 · the rule. Same two tables, new rule: keep every left row, matched or not. The left table is the one we protect.
  2. Step 2 · the matches. Keys 1 and 4 match as before, and their result rows come across complete with val from both sides.
  3. Step 3 · no match, but kept. Keys 2 and 3 find nothing on the right (open circles), but a left join keeps them anyway and fills the right columns with NULL (the dark cells).
  4. Step 4 · right rows with no match. Keys 5 and 6 exist only on the right. A left join does not keep extra right rows, so they are dropped.
  5. Step 5 · the result. Four rows out, one per left row, two of them carrying NULL. A left join never loses a row from the left table, but it can hand you missing values to reckon with.

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.

One row, many matches: the join multiplies

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

Scroll to build the diagram, one step at a time.

left_tableidval1L12L23L34L4right_dupidval1R11R24R35R46R5LEFT JOIN resultidL.valR.val1L1R11L1R22L2NULL3L3NULL4L4R3
Step 1 · a duplicated key Here the right table has key 1 twice (R1 and R2). This is the case that quietly inflates row counts.
Step 2 · one-to-many Left key 1 matches both right rows, so it produces two result rows. One left row became two: the join multiplied it.
Step 3 · the other match Key 4 matches a single right row, so it stays one row. Only the duplicated key multiplied.
Step 4 · no match, but kept Keys 2 and 3 still have no match and are kept with NULL, exactly as a plain left join.
Step 5 · the result Five rows out from four left rows. If you were counting or summing, that extra L1 row double-counts. This is why you check the key is unique on the side you join to.
Text version of this diagram
  1. Step 1 · a duplicated key. Here the right table has key 1 twice (R1 and R2). This is the case that quietly inflates row counts.
  2. Step 2 · one-to-many. Left key 1 matches both right rows, so it produces two result rows. One left row became two: the join multiplied it.
  3. Step 3 · the other match. Key 4 matches a single right row, so it stays one row. Only the duplicated key multiplied.
  4. Step 4 · no match, but kept. Keys 2 and 3 still have no match and are kept with NULL, exactly as a plain left join.
  5. Step 5 · the result. Five rows out from four left rows. If you were counting or summing, that extra L1 row double-counts. This is why you check the key is unique on the side you join to.

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.

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.

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.

Anti-join: keep only the left rows with no match

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

Scroll to build the diagram, one step at a time.

left_tableidval1L12L23L34L4right_tableidval1R14R25R36R4Anti-join resultidL.val2L23L3
Step 1 · start from a left join An anti-join is a left join with a twist. First, line up the two tables the same way.
Step 2 · the matches Keys 1 and 4 match on the right. These are exactly the rows an anti-join wants to throw away.
Step 3 · the non-matches Keys 2 and 3 have no match on the right (open circles). After a left join, their right columns are NULL.
Step 4 · WHERE r.id IS NULL The WHERE r.id IS NULL filter keeps only the rows whose right side never matched. The matched rows drop out, and you are left with the left-only keys 2 and 3.
Text version of this diagram
  1. Step 1 · start from a left join. An anti-join is a left join with a twist. First, line up the two tables the same way.
  2. Step 2 · the matches. Keys 1 and 4 match on the right. These are exactly the rows an anti-join wants to throw away.
  3. Step 3 · the non-matches. Keys 2 and 3 have no match on the right (open circles). After a left join, their right columns are NULL.
  4. Step 4 · WHERE r.id IS NULL. The WHERE r.id IS NULL filter keeps only the rows whose right side never matched. The matched rows drop out, and you are left with the left-only keys 2 and 3.

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.

Joining 7: Find large towns that are NOT county seats

Scenario: Identify major population centers that lack county seat status. Show town, state, population_2020_census, and primary_county.

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.

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.