Course contentsShow
← Data Science
Lesson 962 of 2,14525. SQL Subqueries and CTEsFree lesson

NOT IN with Subqueries

Exclude rows where column values match any subquery result using NOT IN, and understand NULL-related pitfalls.

NOT IN with Subqueries

You'll learn: How to exclude rows by checking if a column's value does not appear in a subquery's result set—and why NULLs can cause surprising problems.

What is NOT IN with a Subquery?

Just as IN lets you include rows where a column matches any value from a subquery, NOT IN does the opposite: it excludes rows where the column matches any subquery result.

Think of it like a guest list for an exclusive event. IN checks "Are you on the list?" while NOT IN asks "Are you not on the list?"—anyone whose name appears gets turned away.

Basic Syntax

SELECT product_name, category_id
FROM products
WHERE category_id NOT IN (
    SELECT category_id 
    FROM discontinued_categories
);

This returns products whose category_id is not among the discontinued categories.

The NULL Pitfall

Here's where NOT IN gets tricky: if the subquery returns any NULL value, the entire NOT IN expression returns NULL, and no rows will match.

Why? SQL uses three-valued logic (TRUE, FALSE, NULL). When comparing a value against a list containing NULL, SQL can't definitively say "this value is not in the list" because NULL means "unknown."

-- ⚠️ Returns ZERO rows if any order has NULL customer_id
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Solution: Filter out NULLs in the subquery:

WHERE customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE customer_id IS NOT NULL
);

Key Takeaway: NOT IN excludes rows matching subquery results but fails silently when NULLs are present—always filter NULLs in the subquery or use NOT EXISTS as a safer alternative.