Subqueries: Basic Patterns

IN Subquery: Find Taylor Swift Listeners

Key Rule: Inside-Out Execution: Inner queries finish before outer queries begin

-- Find users who listen to Taylor Swift songs.
-- Reads inside-out: Step 1 first, Step 3 last.

-- Step 3 (LAST): pick names for the users found in Step 2.
SELECT name
FROM Users
WHERE user_id IN (
    -- Step 2: distinct users who listened to those songs.
    SELECT DISTINCT user_id
    FROM Listens
    WHERE song_id IN (
        -- Step 1 (FIRST): all Taylor Swift song IDs. Returns {1, 2, 9}.
        SELECT song_id
        FROM Songs
        WHERE artist = 'Taylor Swift'
    )
)

Walking the Data Inside-Out

The three nested SELECTs run innermost first: filter the songs, find the listeners, then look up their names.

NOT IN Subquery: Non-Taylor Swift Fans

-- Find users who never listened to any Taylor Swift song.

-- Step 3 (LAST): pick names for users NOT in the Step 2 list.
SELECT name
FROM Users
WHERE user_id NOT IN (
    -- Step 2: distinct users who listened to those songs.
    SELECT DISTINCT user_id
    FROM Listens
    WHERE song_id IN (
        -- Step 1: all Taylor Swift song IDs.
        SELECT song_id
        FROM Songs
        WHERE artist = 'Taylor Swift'
    )
)

Using the same inner queries, but inverting at the end:

Step 1 & 2: Same as above → Users {1, 2, 3} listened to Taylor Swift

Step 3: WHERE user_id NOT IN {1, 2, 3} · 4 users → 1 row (the three who listened are crossed out)

Scalar Subquery: Above Average Ratings

EXISTS: Early Stop Optimization

-- Check if ANY high-value listeners exist.

SELECT 'High-value listeners found!' AS result
-- EXISTS returns TRUE as soon as ANY row matches.
WHERE EXISTS (
    -- SELECT 1 is convention; the value is ignored.
    SELECT 1
    FROM Listens
    -- Stops at the FIRST row where rating > 4.5 (efficient).
    WHERE rating > 4.5
)

-- INEFFICIENT: must scan ALL rows before answering.

SELECT 'High-value listeners found!' AS result
WHERE (
    -- COUNT(*) tallies ALL matching rows (no early stop).
    SELECT COUNT(*)
    FROM Listens
    -- Scans the entire table.
    WHERE rating > 4.5
) > 0  -- Compare the final count to 0.

Performance Comparison:

• EXISTS: 4 rows checked, stops early

• COUNT(*): 9 rows checked, no early stop

• Savings: 56% fewer rows to examine

• Best for: Presence checks, not quantity

Subquery Types Comparison

Key Rules

1. Inside-Out Execution: Inner queries complete before outer queries start

2. Return Types: Single value (scalar), multiple values (IN), or existence check (EXISTS)

3. Independence: Basic subqueries run once, independently of the outer query

4. NULL Handling: NULL in NOT IN can eliminate all results

5. Performance: Often rewritten as JOINs by the optimizer

Common Patterns

• IN/NOT IN: Check if a value exists in a list

• EXISTS/NOT EXISTS: Check if any rows match a condition

• Scalar Comparison: Compare against a single calculated value

• Nested Levels: Subqueries within subqueries

Common Mistakes

The NULL in NOT IN Trap

-- WRONG: Daffy has one unrated listen, so the inner result contains a NULL,
-- and a NULL inside a NOT IN list silently collapses the result to 0 rows.
WHERE rating NOT IN (
    SELECT rating FROM Listens WHERE user_id = 3
)

-- CORRECT: filter the NULL out of the subquery first.
WHERE rating NOT IN (
    SELECT rating FROM Listens WHERE user_id = 3
    AND rating IS NOT NULL
)

Why This Happens (Step by Step)

-- Check: is 4.5 not one of Daffy's ratings {2.9, 4.9, NULL}?
4.5 NOT IN (2.9, 4.9, NULL)
-- You expect: TRUE (4.5 isn't 2.9 or 4.9)

-- SQL expands NOT IN to:
4.5 != 2.9 AND 4.5 != 4.9 AND 4.5 != NULL

-- Evaluates to:
TRUE AND TRUE AND UNKNOWN

-- Final result:
UNKNOWN  -- Not TRUE, so filtered out!

Real Example with Our Data

-- Find every listen NOT rated the way Daffy rated something.

-- WRONG: Daffy's ratings include a NULL, so this returns 0 rows.
SELECT listen_id, rating FROM Listens
WHERE rating NOT IN (
    -- Daffy's ratings: 2.9, 4.9, and one NULL.
    SELECT rating FROM Listens WHERE user_id = 3
)

-- CORRECT: filter the NULL out of the subquery first.
SELECT listen_id, rating FROM Listens
WHERE rating NOT IN (
    -- Critical filter to avoid the NOT IN NULL trap.
    SELECT rating FROM Listens WHERE user_id = 3
    AND rating IS NOT NULL
)

Result Difference:

• Wrong query: 0 rows (silent failure!)

• Correct query: 6 rows (every listen rated something other than 2.9 or 4.9)

Safe Patterns to Use Instead

-- Option 1: Filter NULLs (shown above)
WHERE id NOT IN (SELECT id FROM TABLE WHERE id IS NOT NULL)

-- Option 2: Use NOT EXISTS (NULL-safe)
WHERE NOT EXISTS (
    SELECT 1 FROM Listens l 
    WHERE l.song_id = s.song_id
)

-- Option 3: Use LEFT JOIN with NULL check
FROM Songs s
LEFT JOIN Listens l ON s.song_id = l.song_id
WHERE l.song_id IS NULL

Remember: Always check if your NOT IN subquery can return NULL!

The Multiple Values Error

-- WRONG: subquery returns multiple rows, but `=` expects one.
WHERE rating = (SELECT rating FROM Listens)

-- CORRECT: use IN for multiple values.
WHERE rating IN (SELECT rating FROM Listens)

-- OR: use scalar aggregation to get a single value.
WHERE rating = (SELECT MAX(rating) FROM Listens)

Problem: = expects a single value, not a list