Baldwin Apps

Posted on May 30

SQL Pattern Series #1: The Presence Pattern

#sql #database #beginners #tutorial

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
relationships
matching rows

instead of building lists unnecessarily.

That shift often makes queries easier to reason about.

A Note on Performance

Modern database optimizers are extremely sophisticated.

In many systems, IN and EXISTS may be rewritten into similar execution plans.

As a result:

The same result does not necessarily mean the same execution strategy.

And the same syntax does not necessarily mean different performance.

Always validate assumptions with execution plans and real-world testing.

The value of this pattern is primarily conceptual:

existence vs. membership
relationship vs. list
presence vs. values

When I Reach for This Pattern

I typically consider EXISTS when:

I only need to know whether related data exists
The subquery may return many rows
The relationship itself is the focus of the query
I want the query to communicate intent clearly

Examples include:

customers with orders
users with activity
products with transactions
accounts with associated records

Key Takeaway

Many SQL problems become easier when you ask:

Do I need the values?

or:

Do I simply need to know whether they exist?

That small distinction changes how you think about the query.

And sometimes, changing how you think about the problem is more important than changing the syntax.