Subqueries and IN/EXISTS

Filter against dynamic values — and learn why NOT IN silently breaks when a NULL is present

The beer_db styles table defines ABV and IBU ranges for each style. A beer is "above average" for its style if its ABV exceeds the average ABV of all beers in that style. To answer that question, the WHERE clause needs a value that changes per row — the average ABV of THAT beer's style. That is a correlated subquery.\n\nSubqueries can appear in SELECT (scalar value), WHERE (set membership or existence), and FROM (derived table). Each position has different performance and correctness characteristics.\n\nThe critical trap: NOT IN (subquery) uses three-valued logic. If the subquery returns even one NULL, the entire NOT IN expression becomes NULL — meaning it is never TRUE — meaning zero rows pass. Silently.

Scalar Subquery — A Single Value

A scalar subquery returns exactly one row and one column. It can appear anywhere a single value can appear.

-- The overall average ABV across all beers
SELECT AVG(abv) FROM beers;  -- 5.47

-- Use it as a scalar in WHERE
SELECT name, abv
FROM   beers
WHERE  abv > (SELECT AVG(abv) FROM beers)
ORDER BY abv;

IN (subquery) — Set Membership

-- Beers whose style name contains 'IPA'
SELECT name, style, abv
FROM   beers
WHERE  style IN (
    SELECT name FROM styles WHERE name LIKE '%IPA%'
)
ORDER BY style, abv;

Correlated Subquery — Runs Once Per Row

A correlated subquery references a column from the outer query. It re-executes for each row.

-- Beers with ABV above the average for their own style
SELECT b.name, b.style, b.abv,
       ROUND((SELECT AVG(abv) FROM beers b2 WHERE b2.style = b.style), 2) AS style_avg
FROM   beers b
WHERE  b.abv > (SELECT AVG(abv) FROM beers b2 WHERE b2.style = b.style)
ORDER BY b.style, b.abv DESC;

Derived Table in FROM — Pre-Aggregate Once

Computing the same subquery for every row is expensive. Pre-aggregate in FROM instead:

SELECT b.name, b.style, b.abv, avgs.avg_abv
FROM   beers b
INNER JOIN (
    SELECT style, ROUND(AVG(abv), 2) AS avg_abv
    FROM   beers
    GROUP BY style
) AS avgs ON avgs.style = b.style
WHERE  b.abv > avgs.avg_abv
ORDER BY b.style, b.abv DESC;

The NOT IN NULL Trap ⚠️

-- Suppose styles_with_nulls returns (5.5, 7.0, NULL)
WHERE abv NOT IN (5.5, 7.0, NULL)

-- SQL evaluates this as:
-- abv <> 5.5 AND abv <> 7.0 AND abv <> NULL
-- abv <> NULL → NULL (three-valued logic)
-- NULL AND anything → NULL
-- WHERE only keeps TRUE → zero rows returned

-- Fix: use NOT EXISTS instead
WHERE NOT EXISTS (
    SELECT 1 FROM styles s
    WHERE  s.abv_range @> b.abv  -- example condition
)

Correlated subquery

A subquery that references a column from the outer query. Unlike a regular subquery (which is computed once), a correlated subquery is re-executed for each row of the outer query. This makes them potentially slow on large tables — but they express per-row dynamic filtering naturally. The derived table approach (pre-aggregating in FROM with GROUP BY) is usually faster.

NOT IN NULL trap

NOT IN uses equality comparisons under the hood. When the subquery or list contains NULL, the comparison value <> NULL evaluates to NULL (not TRUE or FALSE), because of three-valued logic. WHERE only passes rows where the condition is TRUE — NULL is discarded. Result: zero rows, no error, no warning. This is one of the most dangerous SQL traps in production. Use NOT EXISTS instead — it tests existence, not equality, and is NULL-safe.

🔌 Connect to beer_db

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

psql -U beer_db -d beer_db

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

📐 Scalar Subquery: Beers Above Overall Average ABV

Find all beers with ABV above the overall average ABV across all 400 beers. Use a scalar subquery in the WHERE clause — you should not need to know the average value yourself.

SELECT name, style, abv FROM beers WHERE abv > (SELECT AVG(abv) FROM beers) ORDER BY abv DESC LIMIT 10;

🎯 IN (subquery): Beers Matching IPA Styles

Use IN with a subquery to find all beers whose style matches a style name containing "IPA" in the styles table. This avoids hardcoding the list of IPA style names.

SELECT b.name, b.style, b.abv FROM beers b WHERE b.style IN (SELECT name FROM styles WHERE name LIKE '%IPA%') ORDER BY b.style, b.abv DESC LIMIT 10;

🔄 Correlated Subquery: Above-Average ABV Per Style

Find beers with ABV above the average ABV for their own style. This requires a correlated subquery: the inner SELECT references b.style from the outer query, so it re-runs for each beer with a different style filter.

SELECT b.name, b.style, b.abv, ROUND((SELECT AVG(abv) FROM beers b2 WHERE b2.style = b.style), 2) AS style_avg FROM beers b WHERE b.abv > (SELECT AVG(abv) FROM beers b2 WHERE b2.style = b.style) ORDER BY b.style, b.abv DESC LIMIT 10;

⚡ Rewrite with a Derived Table (Faster)

Rewrite the same query without a correlated subquery. Pre-compute style averages once in a FROM subquery (derived table), then join to it. This avoids re-running the AVG calculation for every beer.

SELECT b.name, b.style, b.abv, avgs.avg_abv FROM beers b INNER JOIN (SELECT style, ROUND(AVG(abv), 2) AS avg_abv FROM beers GROUP BY style) AS avgs ON avgs.style = b.style WHERE b.abv > avgs.avg_abv ORDER BY b.style, b.abv DESC LIMIT 10;

💣 Trigger the NOT IN NULL Trap

Demonstrate the NOT IN NULL trap. Find styles where the description IS NULL in the styles table. Then use NOT IN with the style names to find beers not in those styles — and observe what happens when you manually include a NULL in the list.

SELECT COUNT(*) FROM beers WHERE style NOT IN ('Double IPA', 'Session IPA', 'American IPA');

SELECT COUNT(*) FROM beers WHERE style NOT IN ('Double IPA', 'Session IPA', NULL);

count ------- 357 (1 row) count ------- 0 (1 row)

🛡️ Fix the NULL Trap with NOT EXISTS

Rewrite the previous NOT IN query using NOT EXISTS instead. NOT EXISTS is NULL-safe because it checks existence, not equality. The count should return the same 371 rows even with a NULL present.

SELECT COUNT(*) FROM beers b WHERE NOT EXISTS (SELECT 1 FROM (VALUES ('Double IPA'), ('Session IPA'), (NULL)) AS excluded(style_name) WHERE excluded.style_name = b.style);

count ------- 371 (1 row)

Lab complete! You can now write subqueries safely and efficiently:\n\n\n Scalar subquery (WHERE) : ✅ single dynamic value\n IN (subquery) : ✅ set membership\n Correlated subquery : ✅ per-row dynamic filter\n Derived table in FROM : ✅ pre-aggregate once (faster)\n NOT EXISTS : ✅ NULL-safe anti-membership\n NOT IN NULL trap : ✅ understood and avoided\n

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