INNER JOIN

Link two tables — and understand exactly which rows disappear

The beer_db keeps brewery information in a separate breweries table. Every beer has a brewery_id foreign key pointing to its brewery. To answer "which city brewed this beer?" you must JOIN the two tables.\n\nAn INNER JOIN returns only rows where the ON condition is satisfied on both sides. If a beer has a NULL brewery_id, or its brewery_id points to a brewery that no longer exists, that beer simply vanishes from the result — no error, no warning, just a lower row count.\n\nThe first thing to check after any JOIN is: did the row count change? If it did, you need to understand why.

How INNER JOIN Works

An INNER JOIN combines rows from two tables where the ON condition evaluates to TRUE. Rows that have no match on either side are excluded from the result.

-- Basic syntax
SELECT b.name AS beer, br.name AS brewery, br.city
FROM   beers b
INNER JOIN breweries br ON b.brewery_id = br.id;

Table aliases (b for beers, br for breweries) are essential when column names overlap — both tables have a column called name, so b.name and br.name are unambiguous.

Chaining Three Tables

SELECT b.name  AS beer,
       b.style,
       br.name AS brewery,
       br.city
FROM   beers b
INNER JOIN breweries br ON b.brewery_id = br.id
WHERE  br.country = 'Czech Republic'
ORDER BY br.city, b.name;

What Gets Excluded

| Situation | Result | |---|---| | beer.brewery_id IS NULL | beer excluded | | brewery_id references a deleted brewery | beer excluded | | Brewery exists but has no beers | brewery excluded |

Proving Exclusion with Row Counts

-- Total beers in the table
SELECT COUNT(*) FROM beers;

-- Beers that survive the JOIN (have a valid, matched brewery_id)
SELECT COUNT(*)
FROM   beers b
INNER JOIN breweries br ON b.brewery_id = br.id;

-- If the numbers differ, you have unmatched rows

INNER JOIN

Returns only rows where the ON condition is satisfied on both sides of the join. A row from the left table with no matching row in the right table is excluded. A row from the right table with no matching rows in the left table is also excluded. INNER is the default — writing JOIN without a type keyword means INNER JOIN.

Table alias

A short name for a table within a query, declared after the table name in FROM or JOIN. Aliases are mandatory when two tables share a column name (both have "name"), and they make queries dramatically more readable by shortening long table names. An alias declared in FROM is valid everywhere in that query including WHERE, ORDER BY, and SELECT.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. All JOIN exercises in this lab use the beers and breweries tables.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📊 Count Before the JOIN

Run SELECT COUNT(*) FROM beers to establish the baseline row count before any join. Keep this number in mind — you will compare it to the joined count in the next step.

SELECT COUNT(*) FROM beers;

count ------- 400 (1 row)

🔗 Write Your First INNER JOIN

Join beers to breweries on brewery_id. Select the beer name, style, brewery name, and city. Use aliases b for beers and br for breweries to distinguish the two name columns.

SELECT b.name AS beer, b.style, br.name AS brewery, br.city FROM beers b INNER JOIN breweries br ON b.brewery_id = br.id ORDER BY br.name, b.name LIMIT 5;

🔢 Count After the JOIN — Spot the Difference

Run the same join but with SELECT COUNT(*) instead of projecting columns. Compare this number to the 400 you saw before. If it is the same, all beers have a matched brewery. If it is lower, some beers were silently excluded.

SELECT COUNT(*) FROM beers b INNER JOIN breweries br ON b.brewery_id = br.id;

count ------- 400 (1 row)

🇨🇿 Filter the JOIN Result

List all beers brewed in Czech Republic, showing beer name, style, ABV, and the brewery city. Add a WHERE clause after the JOIN — you can filter on any column from either joined table.

SELECT b.name AS beer, b.style, b.abv, br.city FROM beers b INNER JOIN breweries br ON b.brewery_id = br.id WHERE br.country = 'Czech Republic' ORDER BY br.city, b.name LIMIT 10;

🏭 Join 3 Tables: Beers → Breweries → Ratings

Find the highest-rated breweries by joining three tables: breweries, beers, and beer_ratings. Use COUNT(DISTINCT b.id) for beer count and AVG(r.score) for average rating. This is a JOIN combined with GROUP BY — a very common pattern in analytics.

SELECT br.name AS brewery, br.city, br.country, COUNT(DISTINCT b.id) AS beer_count, ROUND(AVG(r.score), 2) AS avg_rating FROM breweries br INNER JOIN beers b ON b.brewery_id = br.id INNER JOIN beer_ratings r ON r.beer_id = b.id GROUP BY br.id, br.name, br.city, br.country ORDER BY avg_rating DESC LIMIT 10;

Lab complete! You can now confidently join tables and verify the result:\n\n\n INNER JOIN b ON … : ✅ rows matched on both sides\n Table aliases : ✅ b, br — no ambiguous names\n Row count check : ✅ before vs after to detect exclusions\n WHERE after JOIN : ✅ filter on any joined column\n JOIN + GROUP BY : ✅ count / sum across tables\n

Enable JavaScript to run the live terminal and track your progress.