Baldwin Apps

Posted on Jul 7

SQL Pattern Series #12: The Fallback Pattern

#beginners #database #sql #tutorial

Using the first available value when data is incomplete

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

What COALESCE() does
How fallback values help with missing data
Why fallback logic makes query results easier to use
When to use the Fallback Pattern

Real data is often incomplete.

A customer may not have a phone number.

An employee may not have a work email.

A product may not have a display name.

If the query returns NULL, the result may be technically correct.

But it may not be very useful.

That is where the Fallback Pattern becomes useful.

The Problem

Imagine an Employees table:

EmployeeID	WorkEmail	PersonalEmail
1	alice@company.com	alice@gmail.com
2	NULL	bob@gmail.com
3	NULL	NULL

You want to show one preferred email address for each employee.

The rule is:

Use the work email if it exists.
Otherwise use the personal email.
Otherwise show a default message.

That is fallback logic.

The Fallback Pattern

The Fallback Pattern uses COALESCE() to return the first non-NULL value from a list.

The general form is:

COALESCE(expression1, expression2, ..., expressionN)

SQL evaluates the expressions from left to right.

It returns the first value that is not NULL.

If all values are NULL, the result is NULL unless you provide a final fallback value.

Example

SELECT
    EmployeeID,
    COALESCE(
        WorkEmail,
        PersonalEmail,
        'No Email Provided'
    ) AS PreferredEmail
FROM Employees;

This query says:

Use WorkEmail first.

If WorkEmail is missing, use PersonalEmail.

If both are missing, use 'No Email Provided'.

Example Result

EmployeeID	PreferredEmail
1	alice@company.com
2	bob@gmail.com
3	No Email Provided

The result is easier to read because missing values no longer break the output.

Why This Pattern Matters

NULL is meaningful.

It often means:

This value is unknown, unavailable, or not applicable.

But reports and applications often need something usable to display.

The Fallback Pattern helps you create cleaner output without changing the underlying data.

It is especially useful when data quality varies across sources.

Common Uses

The Fallback Pattern appears in many everyday queries.

Preferred contact value

COALESCE(WorkEmail, PersonalEmail, 'No Email Provided')

Display name fallback

COALESCE(DisplayName, Username, Email)

Address fallback

COALESCE(ShippingAddress, BillingAddress)

Numeric fallback

COALESCE(DiscountAmount, 0)

Each example follows the same idea:

Use the best available value.

A Note on Data Types

Most databases require the expressions inside COALESCE() to be compatible.

For example, this is usually fine:

COALESCE(WorkEmail, PersonalEmail, 'No Email Provided')

because all values are text-like.

But mixing unrelated data types may cause errors or implicit conversions.

As always, check the behavior in your database system.

COALESCE vs ISNULL / IFNULL / NVL

Different database systems may offer vendor-specific functions such as:

ISNULL() in SQL Server
IFNULL() in MySQL and SQLite
NVL() in Oracle

COALESCE() is widely supported and works across many SQL systems.

The exact behavior can vary in small ways, especially around data types, but the pattern is the same:

Return the first available value.

Unlike COALESCE(), these vendor-specific alternatives typically accept only two arguments, so they cannot express a multi-step fallback chain in a single call.

When I Reach for This Pattern

I typically use the Fallback Pattern when:

a display value should not be blank
multiple columns can provide the same kind of value
reports need readable defaults
missing data should not break the result
a query needs a safe substitute for NULL

Examples include:

preferred email addresses
customer display names
default labels
optional discounts
missing category names

Key Takeaway

Missing data does not always have to break the result.

The Fallback Pattern helps answer:

What should I use if this value is missing?

COALESCE() gives SQL a clear fallback chain.

Use the first available value.

Keep the result usable.