What is the difference between EXISTS and IN in SQL?

Instruction: Explain membership and existence checks, NULL behavior in NOT IN, and how to compare performance.

Context: Compare SQL EXISTS and IN with a practical query and the common NOT IN NULL trap.

Updated

Example Answer

IN checks whether a value belongs to a set. EXISTS checks whether a subquery returns any row. Either can express some of the same questions, and I use the one that makes the intent clear. I am especially careful with NOT IN because a NULL in the subquery can make the comparison unknown.

Small example

Assume customers(customer_id) and orders(customer_id). To find customers with no matching order:

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

An unrelated NULL in orders.customer_id does not exclude all unmatched customers. By contrast, NOT IN (SELECT customer_id FROM orders) can do so because of SQL’s three-valued logic. Neither syntax is always faster; compare query plans on the database and data you use.

Reference

PostgreSQL: subquery expressions

Related Questions