EXISTS and NOT EXISTS

EXISTS

The EXISTS operator tests whether a subquery returns any rows. It returns TRUE if at least one row is found, FALSE otherwise. EXISTS is used in WHERE and HAVING clauses and is typically paired with correlated subqueries.

-- Find all customers who have placed at least one order
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

The SELECT 1 inside EXISTS is a convention. You could select any expression since EXISTS only cares about row existence, not the actual values. Some developers prefer SELECT * for clarity, but SELECT 1 is marginally more efficient.

-- Find departments that have at least one employee earning over $100,000
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary > 100000
);

EXISTS is particularly efficient because the database engine can stop scanning the subquery table as soon as it finds the first matching row. This short-circuit behavior makes EXISTS faster than IN or JOIN for existence checks, especially on large tables with proper indexes.

NOT EXISTS

NOT EXISTS is the opposite of EXISTS. It returns TRUE when the subquery returns no rows. This is the standard way to implement anti-joins — finding rows in one table that have no matching rows in another.

-- Find customers who have never placed an order
SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS is safer than NOT IN because it handles NULLs correctly. When the subquery returns NULLs, NOT IN returns no rows, but NOT EXISTS works as expected.

-- Find products that have never been reviewed
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.product_id = p.product_id
);

You can combine EXISTS and NOT EXISTS for complex filtering:

-- Find customers who have placed orders but never left a review
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.customer_id = c.customer_id
);

NOT EXISTS is the recommended pattern for anti-joins in production code due to its NULL safety and generally better performance.

EXISTS vs IN

Both EXISTS and IN can solve similar problems, but they work differently. IN compares a value against a list of values. EXISTS checks for row existence with a correlated subquery.

-- Using IN
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Using EXISTS
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Key differences:

Feature	IN	EXISTS
Subquery type	Returns a list of values	Returns rows (correlated)
NULL handling	NOT IN fails with NULLs	Handles NULLs correctly
Performance	Good for small lists	Better for large tables with indexes
Readability	Simpler for value matching	More explicit for existence checks

-- IN is simpler when matching literal values
SELECT * FROM products
WHERE category_id IN (1, 2, 3);

-- EXISTS is better for cross-table existence
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM inventory i WHERE i.product_id = p.product_id AND i.quantity > 0
);

Use IN for simple value matching against a known set. Use EXISTS when checking for related rows in another table, especially when NULLs might be involved.

When to Use Each

Choosing between EXISTS, IN, and JOINs depends on the specific scenario, data distribution, and database engine.

Use EXISTS when:

• Checking for existence of related rows in another table
• The subquery is correlated and the tables are large
• NULL values might be present in the subquery results
• You need anti-join patterns with NOT EXISTS

-- Best use case for EXISTS
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM returns r WHERE r.order_id = o.order_id
);

Use IN when:

• Matching against a static list of values
• The subquery returns a small, finite set
• You want simpler, more readable code

-- Best use case for IN
SELECT * FROM products
WHERE category_id IN (SELECT category_id FROM featured_categories);

Use JOIN when:

• You need columns from the related table
• You want to count or aggregate related rows
• Performance is critical and an index exists

-- Best use case for JOIN
SELECT o.order_id, COUNT(r.return_id) AS return_count
FROM orders o
LEFT JOIN returns r ON o.order_id = r.order_id
GROUP BY o.order_id;

In practice, EXISTS and JOINs are often interchangeable. Start with the most readable approach, then optimize based on performance profiling.