Part 74: The EXISTS & NOT EXISTS Operators

EXISTS tests whether a subquery returns at least one row, stopping immediately upon finding the first match (short-circuit optimization).

1. Syntax

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

2. Why EXISTS Beats IN

Unlike IN, EXISTS does not load the entire list into memory, and is completely immune to NULL comparison pitfalls.

Key Takeaways

• Prefer EXISTS and NOT EXISTS over IN and NOT IN for subquery checks.

In Part 75, we explore Subqueries in the SELECT Clause (Derived Scalar Values)!