Subqueries: Basic Patterns

IN Subquery: Find Taylor Swift Listeners

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

Inside-out is how you read it, not necessarily how the database executes it.

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

The inner queries still return users {1, 2, 3}. NOT IN keeps the remaining user, Pluto.

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: Test Whether a Match Exists

-- Check if ANY high-value listeners exist.

SELECT 'High-value listeners found!' AS result
-- EXISTS is TRUE when the inner query returns any row.
WHERE EXISTS (
    -- SELECT 1 is convention; the value is ignored.
    SELECT 1
    FROM Listens
    WHERE rating > 4.5
)

SELECT 'High-value listeners found!' AS result
WHERE (
    -- COUNT(*) computes how many rows match.
    SELECT COUNT(*)
    FROM Listens
    WHERE rating > 4.5
) > 0  -- Compare the final count to 0.

EXISTS needs only a yes-or-no answer. An executor can stop after finding a match, while COUNT(*) must produce a complete count. As we will see in Module M3, the query optimizer still chooses the physical plan, and we can use EXPLAIN to compare how plans are picked on real data.

Subquery Types Comparison

The NULL in NOT IN Trap

Why This Happens (Step by Step)

For rating 4.5, NOT IN (2.9, 4.9, NULL) requires all three comparisons to hold:

4.5 != 2.9 AND 4.5 != 4.9 AND 4.5 != NULL
-- TRUE AND TRUE AND UNKNOWN → UNKNOWN

WHERE keeps only TRUE. Other ratings may produce FALSE instead of UNKNOWN, but none can make this NOT IN condition TRUE while the list contains NULL.

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
)

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

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)

Takeaway

Match the operator to the inner result: membership, existence, or one value. Check whether the inner result can contain NULL before using NOT IN.