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

EXISTS and NOT EXISTS

Testing for the existence of rows in a subquery and understanding how EXISTS differs from IN.

EXISTS and NOT EXISTS

What You'll Learn

You'll learn how to test whether a subquery returns any rows at all using EXISTS, and how this differs from checking membership with IN.

The Core Idea

EXISTS is a logical operator that returns true if a subquery produces at least one row—regardless of what data those rows contain. It doesn't care about the values; it only checks: "Does this subquery find anything?"

NOT EXISTS is the opposite: it returns true only if the subquery finds nothing.

Think of it like asking "Is there anyone in the room?" rather than "Who is in the room?" You don't need names; you just need a yes/no answer.

How EXISTS Differs from IN

While IN checks if a specific value appears in a subquery's result set:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Boston');

EXISTS simply checks if any matching row exists:

SELECT * FROM orders o 
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.city = 'Boston');

Notice the SELECT 1 inside the EXISTS subquery? The actual column selected doesn't matter—EXISTS only cares whether the subquery returns rows.

Using NOT EXISTS

NOT EXISTS is perfect for finding records that don't have a match:

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

This finds customers with no orders—checking "Is there zero evidence of orders for this customer?"

Performance Note

EXISTS often performs better than IN because it can stop searching as soon as it finds the first matching row, while IN may need to build the entire result set.

Key Takeaway: EXISTS tests whether a subquery finds any rows (not what they contain), making it ideal for existence checks and often more efficient than IN for correlated queries.