Joining Techniques

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.

Setup

Action

Output

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.

Action

Output

If multiple matches

Output

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.

Joining 6: Count towns per county (including counties with zero towns)

Scenario: Some small counties might have no incorporated towns in our database. LEFT JOIN preserves them. Show county, state, the county’s population as county_pop, the town count as num_towns, and the summed town population as total_town_pop (0 when a county has no towns).

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.

Output

Joining 7: 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 8: 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 9: 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.

Combining Multiple Techniques

Real-world queries often combine joins with filtering, grouping, and transformations.

Joining 10: Comprehensive county analysis

Scenario: Create a county dashboard showing each county and state with its year_established, an era of ‘Pioneer’ (established before 1860), ‘Settlement’ (before 1900), or ‘Modern’, the county’s population as county_pop, the town count as num_towns, the summed town population as urban_pop, and the population of each county’s largest town as largest_town_pop.