Baldwin Apps

Posted on Jun 6

SQL Pattern Series #3: The Missing Data Pattern

#beginners #database #sql #tutorial

Finding rows that disappear because of the wrong JOIN

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

Why rows sometimes disappear unexpectedly
The difference between INNER JOIN and LEFT JOIN
How to identify missing related data
When to use the Missing Data Pattern

Most SQL developers eventually encounter a report that looks wrong.

The numbers seem too low.

Rows appear to be missing.

And yet the query runs successfully.

Often the issue is not the data.

It's the JOIN.

The Question Behind the Query

Many SQL queries involve combining information from multiple tables.

The key question becomes:

Do I only want matching rows?

or:

Do I want all rows from one table, even when no match exists?

Those questions lead to very different results.

Matching Rows Only

INNER JOIN

Returns only rows that exist in both tables.

Keep All Rows

LEFT JOIN

Returns all rows from the left table, even when no matching row exists in the right table.

Rows without a match return NULL values for the right-side columns.

The Missing Data Pattern

The Missing Data Pattern appears when data seems to have vanished from a result set.

In many cases, nothing is actually missing.

The query is simply filtering rows through an INNER JOIN.

Imagine a list of orders.

Some orders have matching customers.

Some do not.

An INNER JOIN only returns the orders with matching customer records.

The remaining rows disappear from the result.

A LEFT JOIN reveals those unmatched rows.

The image above summarizes the core idea:

INNER JOIN returns only matching rows
LEFT JOIN keeps all rows from the left table
Missing matches appear as NULL
Sometimes the missing rows are the most important rows

Example Using INNER JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
INNER JOIN Customers c
    ON o.CustomerID = c.ID;

This query returns only orders that have a matching customer record.

Any order without a match is excluded.

Conceptually, SQL asks:

Show me only the rows that exist in both tables.

Example Using LEFT JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
LEFT JOIN Customers c
    ON o.CustomerID = c.ID;

This query keeps every order.

If a matching customer does not exist, the customer columns return NULL.

Conceptually, SQL asks:

Show me every order, whether a matching customer exists or not.

Why This Pattern Matters

Many reporting problems are not caused by bad data.

They are caused by accidental filtering.

Developers often write an INNER JOIN without realizing that unmatched rows are being removed.

The Missing Data Pattern encourages you to ask:

What rows am I losing?
Should unmatched rows be visible?
Is the absence of a match important information?

Sometimes the missing rows are exactly what you're trying to find.

A Note on Investigation

When a report seems incomplete, one of the first things I check is the JOIN type.

A quick comparison between:

INNER JOIN

and

LEFT JOIN

can often reveal whether rows are being filtered unintentionally.

This is especially useful when troubleshooting:

incomplete reports
missing customers
orphaned records
failed imports
data quality issues

When I Reach for This Pattern

I often think about the Missing Data Pattern when I need to find:

customers without orders
orders without customers
employees without managers
products without sales
records missing related data

In these situations, the absence of a relationship is often the most valuable information.

Key Takeaway

When data seems to disappear, ask:

Is the data actually missing?

or:

Is my JOIN hiding it?

Sometimes changing:

INNER JOIN

to:

LEFT JOIN

completely changes what you discover.

And sometimes the missing rows are the story.