Course contentsShow
← Computer Science
Lesson 1604 of 2,87236. Database FundamentalsFree lesson

IN and NOT IN Operators

Using subqueries with IN and NOT IN to test membership in a set of values returned by the subquery.

IN and NOT IN Operators

You'll learn how to use subqueries with IN and NOT IN to test whether a value matches anything in a set returned by another query.

What Are IN and NOT IN with Subqueries?

You already know the IN operator for testing membership in a list of literal values (like WHERE city IN ('Paris', 'London')). But its true power emerges when you pair it with a subquery that returns a set of values dynamically.

The IN operator checks if a value exists anywhere in the result set of the subquery. NOT IN does the opposite—it returns true only if the value is absent from that set.

How It Works

Think of it like a guest list at an event. IN asks "Is this person's name on the list?" while NOT IN asks "Is this person not on the list?"

Syntax:

SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column FROM other_table WHERE condition);

Example:

-- Find all customers who have placed orders
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Find all customers who have NOT placed orders
SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

The subquery runs first, producing a set of values. Then the outer query checks each row against that set.

When to Use It

Use IN when you need to filter based on values that exist in another table or that meet complex conditions. Use NOT IN to find "missing" relationships—like customers who haven't ordered, or products without reviews.

Key Takeaway: IN and NOT IN with subqueries let you test membership against a dynamically-generated set of values, making it easy to filter based on relationships between tables.