1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE returns the first value in its list that is not NULL. Learn how to use it for defaults, fallback columns, safe maths and LEFT JOIN gaps, how it compares with IFNULL, ISNULL and NULLIF, and the traps it does not fix, with queries we ran.

9 min read
SQL COALESCE explained: defaults, NULLIF, IFNULL vs ISNULL and traps

Key takeaways

  • SQL COALESCE takes any number of arguments and returns the first one that is not NULL, or NULL if all are.
  • An empty string is not NULL: combine NULLIF(TRIM(col), '') with COALESCE to skip blanks.
  • Pair NULLIF(x, 0) with COALESCE to avoid division by zero and still show a number.
  • COALESCE is standard SQL. IFNULL and ISNULL are vendor-specific two-argument versions.

NULL means “unknown”, and unknown values spread. Add a NULL to a number and you get NULL. Compare a NULL with anything and the row quietly drops out of your results. SQL COALESCE is the everyday tool for replacing those unknowns with something useful: a default, a fallback column or a zero.

Every query below was run in SQLite 3.50.4 from Python 3.13, and the outputs are copied from that run. COALESCE and NULLIF are standard and behave the same way in PostgreSQL, MySQL, SQL Server and Oracle. Where engines differ, such as division by zero and ISNULL, we say so and note that we did not run those engines. For background on how NULL works, read our SQL NULL values guide.

What is COALESCE?

The syntax is simple:

COALESCE(value1, value2, ..., valueN)

The database checks each argument from left to right and returns the first one that is not NULL. If every argument is NULL, the result is NULL. Think of it as a shorthand for a CASE expression:

CASE WHEN value1 IS NOT NULL THEN value1
     WHEN value2 IS NOT NULL THEN value2
     ELSE value3 END

That equivalence is written into the SQL standard, and it is why COALESCE works everywhere. Our SQL CASE WHEN guide covers the longer form.

Picking the first available value

Customer contact data is often patchy. We stored five contacts, each with a different mix of mobile number, work phone and email, using masked sample numbers:

SELECT name,
       COALESCE(mobile, work_phone, email, 'no contact') AS best
FROM contacts;
name mobile work_phone email best
Asha 98xxxx0101 NULL asha@example.com 98xxxx0101
Ravi NULL 020-xxxx-55 NULL 020-xxxx-55
Li NULL NULL li@example.com li@example.com
Sam NULL NULL NULL no contact
Noor ” (empty) NULL noor@example.com ” (empty)

Four rows behaved as expected. Noor’s row did not: the result was an empty string, even though an email was available.

Empty strings are not NULL

COALESCE only skips NULL. An empty string, a space or the text 'N/A' are all real values to the database. The fix is to turn blanks into NULL first with NULLIF:

SELECT name,
       COALESCE(NULLIF(TRIM(mobile), ''), work_phone, email, 'no contact') AS best
FROM contacts;

With this version Noor’s best contact became noor@example.com, and the other four rows were unchanged. TRIM removes spaces, NULLIF converts the empty result to NULL, and COALESCE moves on. This three-function chain is worth memorising; it cleans most form data. Our SQL string functions guide covers TRIM and friends.

NULL in calculations

Any arithmetic with NULL returns NULL. We applied an optional discount to revenue:

SELECT region, revenue, discount,
       revenue * (1 - discount)              AS raw,
       revenue * (1 - COALESCE(discount, 0)) AS fixed
FROM sales;
region revenue discount raw fixed
North 5000 NULL NULL 5000
South 0 0.1 0.0 0.0
East NULL 0.05 NULL NULL
West 1200 NULL NULL 1200

The raw column lost two regions’ revenue just because no discount was recorded. Wrapping the discount in COALESCE fixed North and West. East stays NULL because the revenue itself is unknown, and that is correct: do not invent a zero for a value that might be large.

NULLIF and division by zero

NULLIF(a, b) returns NULL when a equals b, and a otherwise. Its classic job is protecting a division:

SELECT region, revenue, orders,
       revenue * 1.0 / NULLIF(orders, 0)              AS aov,
       COALESCE(revenue * 1.0 / NULLIF(orders, 0), 0) AS aov_or_0
FROM sales;

North returned an average order value of 100.0. South and West had zero orders, so NULLIF turned the divisor into NULL and the result into NULL instead of an error; COALESCE then displayed 0. Engines differ here: SQLite returned NULL even for a bare 5000/0 in our test, while PostgreSQL and SQL Server raise a division-by-zero error (we did not run those). Using NULLIF makes the behaviour the same everywhere.

Tip: decide whether “no orders” should show 0 or a blank. A zero average order value suggests orders worth nothing, so many reports keep NULL and display a dash.

COALESCE with aggregates

Aggregate functions skip NULLs, which changes averages more than people expect:

SELECT SUM(revenue), COUNT(revenue), COUNT(*),
       AVG(revenue), AVG(COALESCE(revenue, 0))
FROM sales;
(6200, 3, 4, 2066.6666666666665, 1550.0)

AVG divided by the three known values, while the COALESCE version treated East as zero and divided by four. Neither is automatically right; choose based on whether a missing value means “zero” or “unknown”. There is also a quieter trap: SUM over no rows returns NULL, not 0. Our query for a region that does not exist returned NULL until we wrapped it as COALESCE(SUM(revenue), 0).

Filling LEFT JOIN gaps

A LEFT JOIN fills missing matches with NULL. Only North had a refund in our data:

SELECT s.region, r.amount AS refund, COALESCE(r.amount, 0) AS refund0
FROM sales s
LEFT JOIN refunds r ON r.region = s.region;

North showed 300, and the other three regions showed NULL in the raw column but 0 in the COALESCE column. That makes the column safe to subtract from revenue. Join behaviour is explained in our SQL joins guide and our new SQL self join guide, which uses COALESCE to label the top manager.

The WHERE trap COALESCE fixes

We counted contacts whose mobile is not 98xxxx0101. Four of five contacts qualify, but the plain query disagreed:

SELECT COUNT(*) FROM contacts WHERE mobile <> '98xxxx0101';                 -- 1
SELECT COUNT(*) FROM contacts WHERE COALESCE(mobile, '') <> '98xxxx0101';   -- 4
SELECT COUNT(*) FROM contacts WHERE mobile IS NOT '98xxxx0101';             -- 4 (SQLite)

The plain query returned 1 because a comparison with NULL is unknown, and unknown rows are filtered out. COALESCE fixed it. The standard null-safe alternative is IS DISTINCT FROM in PostgreSQL and recent SQL Server versions, and SQLite accepts IS NOT as we used. Be aware that wrapping a column in COALESCE inside WHERE can stop the database using an index on it.

COALESCE vs IFNULL vs ISNULL vs NVL

Function Arguments Where Note
COALESCE Any number All major databases Standard SQL, the portable choice
IFNULL Exactly 2 SQLite, MySQL Three arguments raised “wrong number of arguments” in SQLite
ISNULL Exactly 2 SQL Server Result takes the first argument’s type, so long fallbacks can be cut short
NVL Exactly 2 Oracle Oracle’s two-argument version
NULLIF Exactly 2 All major databases The reverse job: turns a value into NULL

Watch out for MySQL: its ISNULL(x) takes one argument and returns 1 or 0, which is a test, not a replacement. When in doubt, write COALESCE.

Types and short-circuiting

Two finer points came out of our tests. First, the arguments should share a type. SQLite is flexible and returned the integer 5 from COALESCE(NULL, 5, 'x'), but stricter engines such as PostgreSQL reject mixed types, so cast explicitly. Second, COALESCE stops at the first non-NULL value. We registered a Python function that logs each call and ran COALESCE('first', slow('second')); the function was never called. Put cheap columns first and expensive subqueries last.

SQL COALESCE checklist

  • Decide what NULL means for each column before replacing it: missing, not applicable or truly zero.
  • Clean blanks first with NULLIF(TRIM(col), '') so empty strings do not block the fallback.
  • Protect every division with NULLIF(divisor, 0), whatever your current database does.
  • Wrap totals as COALESCE(SUM(col), 0) when an empty result should read as zero.
  • Keep argument types consistent and cast where needed, so the query ports between engines.
  • Watch WHERE clauses: use COALESCE or a null-safe comparison, and check the query plan afterwards.
  • Test with NULL rows in your fixtures; most COALESCE bugs only appear when real data has gaps.

Final thoughts

SQL COALESCE is small but it touches almost every report you will write. Use it to choose fallback columns, to keep NULL out of arithmetic, to fill LEFT JOIN gaps and to make sums of nothing read as zero. Combine it with NULLIF to clean blanks and protect divisions. And before reaching for it, ask whether a missing value really means zero, because hiding an unknown can be worse than showing it.

Frequently asked questions

What does COALESCE do in SQL?

It returns the first non-NULL value from its list of arguments. If every argument is NULL, it returns NULL.

What is the difference between COALESCE and ISNULL?

COALESCE is standard SQL and accepts any number of arguments. ISNULL is SQL Server specific, takes exactly two, and returns the type of the first argument, which can truncate longer values.

Does COALESCE treat an empty string as NULL?

No. An empty string is a real value. Use COALESCE(NULLIF(TRIM(col), ”), fallback) to treat blanks as missing.

How do I avoid division by zero in SQL?

Divide by NULLIF(divisor, 0) so a zero becomes NULL and the result is NULL instead of an error. Wrap it in COALESCE if you want to display a default such as 0.

Why does SUM return NULL instead of 0?

SUM over zero rows, or over only NULL values, returns NULL. Use COALESCE(SUM(col), 0) when an empty result should read as zero.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
Software Development

HTTP Methods Explained: GET, POST, PUT, PATCH, DELETE, Tested on a Live Server

HTTP methods explained with a small server we ran: GET, POST, PUT, PATCH, DELETE, HEAD and OPTIONS, safe vs idempotent,…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Software Development

JavaScript Sort Array: Numbers, Objects, Strings and Why 10 Comes Before 5

How to sort an array in JavaScript correctly: numbers with a compare function, objects by one or more fields, strings…

Read article →
Software Development

Python String Methods: Cheat Sheet, Real Examples and the strip() Trap

Python string methods with tested examples: split, join, strip, replace, find, startswith, is-methods, casefold, partition and translate, plus the strip()…

Read article →
Software Development

Python zip() Explained: Loop Two Lists at Once, Unzip, strict=True and Traps

Python zip() pairs items from several lists in one loop. Tested examples for dicts, unzipping with *, strict=True, zip_longest, sliding…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
Software Development

UTF-8 Encoding Explained: How Text Becomes Bytes and How to Fix Garbled Text

UTF-8 encoding explained with byte-level examples we ran in Python: how characters become 1 to 4 bytes, why café turns…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Databases & SQL

Delete Duplicate Rows in SQL: 5 Safe Methods, Tested Step by Step

How to delete duplicate rows in SQL safely: find them, preview them, then remove them with ROW_NUMBER, MIN(id), EXISTS, rowid…

Read article →
Databases & SQL

SQL RANK vs DENSE_RANK vs ROW_NUMBER: Differences with Tested Examples

SQL RANK vs DENSE_RANK vs ROW_NUMBER in one table: how each handles ties, top-N per group, the second-highest salary trap…

Read article →
Software Development

JavaScript Optional Chaining (?.) Explained: 8 Examples, ?? Defaults and Traps

JavaScript optional chaining stops “Cannot read properties of undefined” errors. 8 tested examples for properties, arrays and methods, ?? vs…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Databases & SQL

SQL COALESCE Explained: Defaults, NULLIF, IFNULL vs ISNULL and Traps, Tested

SQL COALESCE explained with queries we ran in SQLite 3.50: first non-null values, empty strings, NULL in maths, NULLIF for…

Read article →
Databases & SQL

SQL Self Join Explained: Employee-Manager, Pairs and Row Comparisons, Tested

SQL self join explained with queries we ran in SQLite 3.50: employee and manager, earning more than the manager, unique…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →
Artificial Intelligence

Prompt Injection Explained: How AI Apps Get Tricked and How to Defend Them

Prompt injection explained for builders: direct and indirect attacks, why keyword filters fail (our test blocked 1 of 4), and…

Read article →
DevOps

DNS Records Explained: A, AAAA, CNAME, MX, TXT and TTL With Real Lookups

DNS records explained with lookups we ran: how resolution works, A and AAAA, CNAME rules, MX and null MX, TXT…

Read article →
DevOps

Feature Flags Explained: Safe Releases, Gradual Rollouts and Flag Hygiene

Feature flags explained with a rollout simulation we ran: flag types, percentage rollouts with stable hashing, kill switches, allow lists,…

Read article →
Test Automation

Test Data Generation With Python Faker: Realistic, Repeatable and Safe Data

Test data generation with Python Faker, run and measured: seeding for repeatable data, locales, unique values, domain-shaped records, edge cases…

Read article →
Software Testing

Shift-Left Testing Explained: Find Bugs Earlier Without Slowing the Team

Shift-left testing explained: what it means, why early defects are cheaper, practices from requirement reviews to CI checks, the tester's…

Read article →
Artificial Intelligence

LLM Structured Output Explained: Getting Reliable JSON From AI Models

LLM structured output explained with validation we ran in Pydantic: JSON mode and schemas, constrained decoding, the ways model output…

Read article →
Test Automation

API Contract Testing Explained: Catch Breaking Changes Before They Ship

API contract testing explained with a schema check we ran: what a contract is, consumer-driven contracts, Pact, OpenAPI, which changes…

Read article →
Software Testing

Risk-Based Testing Explained: Score Risks, Build a Matrix and Test What Matters

Risk-based testing explained step by step: identifying risks, scoring likelihood and impact, building a risk matrix, mapping risk to test…

Read article →