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'
    )
)

• Three layers of queries

• Each feeds results outward

• Set operations at each level

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'
    )
    -- CRITICAL: filter NULLs to avoid the NOT IN empty-result trap.
    AND user_id IS NOT NULL
)

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: Get Names for Users NOT IN {1, 2, 3}

Scalar Subquery: Above Average Ratings

-- Find listens with above-average ratings.

SELECT u.name, l.rating
FROM Users u
JOIN Listens l ON u.user_id = l.user_id
-- Compare each rating to a single value (scalar subquery).
WHERE l.rating > (
    -- Step 1: average rating across all Listens. Returns ONE value: 4.2.
    -- AVG ignores NULL values. Runs ONCE, cached, reused for every outer row.
    SELECT AVG(rating)
    FROM Listens
)

• Scalar subquery returns a single value

• Used in comparisons like any constant

• NULL handling is critical for AVG

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 Death Trap

-- WRONG: if the subquery returns ANY NULL, the entire result becomes empty.
-- NULL inside a NOT IN list silently returns 0 rows.
WHERE user_id NOT IN (
    -- May contain NULL user_ids.
    SELECT user_id FROM Listens
)

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

Why This Happens (Step by Step)

-- Check: Is Pluto's ID (4) not in list?
4 NOT IN (1, 2, 3, NULL)
-- You expect: TRUE (Pluto not listed)

-- SQL expands NOT IN to:
4 != 1 AND 4 != 2 AND 4 != 3 AND 4 != NULL

-- Evaluates to:
TRUE AND TRUE AND TRUE AND UNKNOWN

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

Real Example with Our Data

-- Find songs never listened to.

-- WRONG: returns empty if any song_id in Listens could be NULL.
SELECT title FROM Songs
WHERE song_id NOT IN (
    -- Has songs 1, 2, 6, 7, 8 plus potential NULLs.
    SELECT song_id FROM Listens
)

-- CORRECT: filter NULLs out of the subquery first.
SELECT title FROM Songs
WHERE song_id NOT IN (
    -- Critical filter to avoid the NOT IN NULL trap.
    SELECT song_id FROM Listens
    WHERE song_id IS NOT NULL
)

Result Difference:

• Wrong query: 0 rows (silent failure!)

• Correct query: 5 rows (songs 3,4,5,9,10)

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