SQL Pattern Series #1: The Presence Pattern

Thinking in terms of existence instead of lists SQL Pattern Series #1 of 21 A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems. What You'll Learn In this article you'll learn: When EXISTS and IN solve the same problem The difference between set membership and existence Why the underlying mental model matters When I typically reach for EXISTS Most SQL developers write a query like this at some point: SELECT c . CustomerID , c . CustomerName FROM Customers c WHERE c . CustomerID IN ( SELECT o . CustomerID FROM Orders o ); And it works. But sometimes it isn't the best way to think about the problem. The Question Behind the Query Many SQL problems can be framed in two different ways. Set Membership Is this value in a set? WHERE CustomerID IN (...) Existence Does at least one matching row exist? WHERE EXISTS (...) Both approaches often return the same result. But they represent different mental models. The Presence Pattern The Presence Pattern is useful when you do not actually care about the values being returned from a related table. You only care whether a matching row exists. For example: Customers who have placed an order Users who have logged in Employees assigned to a project Products that have sales In these cases, the question is often: Does a related row exist? rather than: What values are contained in this list? Example Using EXISTS SELECT c . CustomerID , c . CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o . CustomerID = c . CustomerID ); The subquery is correlated to the outer query. Conceptually, SQL asks: For this customer, does at least one matching order exist? As soon as the answer becomes true, the condition is satisfied. Why This Pattern Matters Many SQL developers initially learn syntax. Over time, they discover that query writing is really about choosing the right mental model. The Presence Pattern encourages you to think in terms of: existence relationshi