Baldwin Apps

Posted on Jul 21

SQL Pattern Series #16: The Missing Match Pattern

#beginners #database #sql #tutorial

Finding rows that have no related match

SQL Pattern Series #16 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:

How to find rows that do not have related records
Why LEFT JOIN and NULL often appear together
When missing relationships reveal important information
How the Missing Match Pattern differs from the Presence Pattern

Many SQL queries focus on what exists.

Sometimes the more interesting question is:

What is missing?

For example:

Customers who never placed an order
Users who never logged in
Products that have never been sold
Employees without managers
Accounts with no activity

That is where the Missing Match Pattern becomes useful.

The Problem

Imagine two tables:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

OrderID	CustomerID
101	1
102	2

Alice and Bob have orders.

Charlie does not.

Suppose you want to find:

Which customers have never placed an order?

The answer is not stored directly.

You must identify the missing relationship.

The Missing Match Pattern

A common solution uses:

LEFT JOIN

combined with a check for:

IS NULL

The pattern is:

LEFT JOIN related_table
WHERE related_table.id IS NULL

This returns rows that failed to find a match.

Example

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

The query keeps all customers.

If a matching order exists, the order columns are populated.

If no match exists, the order columns become:

NULL

The final filter keeps only those unmatched rows.

Example Result

CustomerID	CustomerName
3	Charlie

Charlie appears because no matching order was found.

That missing relationship is exactly what we were looking for.

Why This Pattern Matters

Many important business questions involve absence rather than presence.

Examples include:

inactive customers
unsold products
users who never completed onboarding
employees without assignments
projects without owners

The Missing Match Pattern helps surface these gaps.

Presence vs Missing Match

The Presence Pattern asks:

Does a matching row exist?

The Missing Match Pattern asks:

Does a matching row NOT exist?

For example:

Presence Pattern

WHERE EXISTS (...)

Returns rows that have matches.

Missing Match Pattern

LEFT JOIN ...
WHERE related_id IS NULL

Returns rows that do not have matches.

They are closely related patterns that answer opposite questions.

Common Uses

The Missing Match Pattern appears frequently in auditing and reporting.

Customers with no orders

LEFT JOIN Orders
WHERE Orders.CustomerID IS NULL

Products with no sales

LEFT JOIN Sales
WHERE Sales.ProductID IS NULL

Employees without managers

LEFT JOIN Employees m
WHERE m.EmployeeID IS NULL

Users who never logged in

LEFT JOIN LoginHistory
WHERE LoginHistory.UserID IS NULL

In each case, the missing match is the result.

A Note on NOT EXISTS

Many developers also solve this problem using:

NOT EXISTS

For example:

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Many experienced SQL developers prefer NOT EXISTS because it directly communicates the intent of finding rows with no matching related records.

This often expresses the intent very clearly:

Return customers for whom no matching order exists.

Both approaches are common.

The best choice often depends on readability, team conventions, and the specific database system.

When I Reach for This Pattern

I typically use the Missing Match Pattern when:

looking for inactive records
validating data completeness
auditing relationships
identifying orphaned records
finding things that should exist but do not

Examples include:

customers with no orders
products with no sales
users with no activity
projects without owners
records missing related data

Key Takeaway

Many SQL questions are really asking:

What should have a match, but doesn't?

The Missing Match Pattern helps answer that question.

Find the relationship.

Keep the failures.

Sometimes the missing rows are the report.