LEFT JOIN and Finding Gaps

Keep all rows from the left table — then use IS NULL to find the ones with no match

The beer_db has a festival_appearances table that records which beers appeared at which festivals. Some beers have never appeared at any festival. An INNER JOIN on beers to festival_appearances would silently exclude those beers — exactly what you do NOT want when the question is "which beers have never appeared?"\n\nLEFT JOIN changes the contract: every row from the left table is kept, even if no matching row exists on the right. When there is no match, all right-side columns are filled with NULL. That NULL is the signal — filter for it with WHERE right_table.id IS NULL and you have your list of gaps.

LEFT JOIN: Keeping All Left-Side Rows

With INNER JOIN, rows that have no match are excluded. LEFT JOIN keeps every row from the left table — and fills right-side columns with NULL when no match exists.

-- All beers, showing festival info where it exists (NULL where it doesn't)
SELECT b.name, b.style, fa.festival_id
FROM   beers b
LEFT JOIN festival_appearances fa ON fa.beer_id = b.id
ORDER BY b.name
LIMIT 10;

The Anti-Join Pattern: Finding Rows with No Match

The most important use of LEFT JOIN is finding gaps — rows on the left with no match on the right.

-- Beers that have NEVER appeared at any festival
SELECT b.name, b.style
FROM   beers b
LEFT JOIN festival_appearances fa ON fa.beer_id = b.id
WHERE  fa.beer_id IS NULL       -- ← the anti-join filter
ORDER BY b.name;

The logic: LEFT JOIN fills fa.beer_id with NULL for beers with no festival appearance. WHERE fa.beer_id IS NULL then keeps only those NULLs — the "never appeared" beers.

The Same Result with NOT EXISTS

SELECT b.name, b.style
FROM   beers b
WHERE  NOT EXISTS (
    SELECT 1
    FROM   festival_appearances fa
    WHERE  fa.beer_id = b.id
)
ORDER BY b.name;

Both patterns return identical rows. NOT EXISTS often reads more clearly for humans; LEFT JOIN + IS NULL is more commonly seen in legacy code.

RIGHT JOIN and FULL OUTER JOIN

-- RIGHT JOIN: all rows from the RIGHT table, NULLs on the left where unmatched
-- Equivalent to swapping the tables in a LEFT JOIN — rarely needed
SELECT b.name, fa.festival_id
FROM   festival_appearances fa
RIGHT JOIN beers b ON b.id = fa.beer_id;   -- same as LEFT JOIN above, tables swapped

-- FULL OUTER JOIN: all rows from both sides, NULLs wherever there is no match
SELECT b.name, fa.festival_id
FROM   beers b
FULL OUTER JOIN festival_appearances fa ON b.id = fa.beer_id;

LEFT JOIN

Returns all rows from the left (first) table, plus matching rows from the right table. When no match exists in the right table, right-side columns are filled with NULL. This preserves the complete left-side dataset, letting you identify which rows have related data and which do not.

Anti-join pattern

A LEFT JOIN followed by WHERE right_table.id IS NULL — used to find rows in the left table that have NO matching row in the right table. The NULL signals "no match was found". This is one of the most useful patterns in SQL analytics: finding customers with no orders, products with no sales, beers with no reviews, users with no activity.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. This lab uses the beers and festival_appearances tables.

psql -U beer_db -d beer_db

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

📊 Understand the Festival Data

Before joining, understand the tables. Check how many beers are in the database and how many festival appearances exist. These baseline numbers will make the join results meaningful.

SELECT COUNT(*) AS total_beers FROM beers;

SELECT COUNT(*) AS total_appearances FROM festival_appearances;

total_beers ------------- 400 (1 row) total_appearances ------------------- 200 (1 row)

🔗 LEFT JOIN to See All Beers

Write a LEFT JOIN from beers to festival_appearances. Show the beer name, style, and festival_id. Beers with no festival appearances will show NULL for festival_id.

SELECT b.name, b.style, fa.festival_id FROM beers b LEFT JOIN festival_appearances fa ON fa.beer_id = b.id ORDER BY fa.festival_id NULLS LAST, b.name LIMIT 10;

🕵️ Find Beers That Never Appeared — Anti-Join

Now apply the anti-join filter: add WHERE fa.beer_id IS NULL to find beers with no festival appearance. Count them too.

SELECT b.name, b.style FROM beers b LEFT JOIN festival_appearances fa ON fa.beer_id = b.id WHERE fa.beer_id IS NULL ORDER BY b.name LIMIT 10;

SELECT COUNT(*) FROM beers b LEFT JOIN festival_appearances fa ON fa.beer_id = b.id WHERE fa.beer_id IS NULL;

name | style ------------------------+---------------- Anniversary Bock | English Brown Anniversary Gose | Czech Premium ... (10 rows) count ------- 237 (1 row)

🔄 Same Result with NOT EXISTS

Write the same "beers that never appeared at a festival" query using NOT EXISTS instead of LEFT JOIN + IS NULL. The count must be identical.

SELECT COUNT(*) FROM beers b WHERE NOT EXISTS (SELECT 1 FROM festival_appearances fa WHERE fa.beer_id = b.id);

🎪 Count Appearances Per Beer

How many times has each beer appeared at a festival? Show beer name, style, and appearance count. Include beers with zero appearances — use LEFT JOIN and COUNT on the right-side column.

SELECT b.name, b.style, COUNT(fa.beer_id) AS appearances FROM beers b LEFT JOIN festival_appearances fa ON fa.beer_id = b.id GROUP BY b.id, b.name, b.style ORDER BY appearances DESC, b.name LIMIT 10;

Lab complete! You can now find rows with no related data:\n\n\n LEFT JOIN : ✅ keeps all left-side rows\n WHERE right.col IS NULL : ✅ anti-join — finds the gaps\n NOT EXISTS : ✅ same result, often clearer\n LEFT JOIN + COUNT : ✅ zero-safe aggregation\n

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