Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 125 ยท Functions and Expressions

COALESCE, IFNULL and NULLIF

Replace missing values with defaults using COALESCE and IFNULL, turn placeholder values into NULL with NULLIF, and avoid division-by-zero errors in SQL.

Missing values are everywhere: an employee without an email, a project without an end date, a course nobody teaches yet. In SQL they're NULL, and as you saw in Working with NULL, NULL spreads through calculations: NULL + 1 is NULL, 'Mail: ' || NULL is NULL, and a comparison with NULL is never true.

Three small functions give you control over that behavior. COALESCE replaces a NULL with a value of your choice, IFNULL is a two-argument shortcut for the same thing, and NULLIF does the opposite: it turns a specific value into NULL. Together they solve some of the most common everyday problems in SQL, including the famous division by zero.

COALESCE: the first value that isn't NULL

COALESCE(a, b, c, ...) looks at its arguments from left to right and returns the first one that isn't NULL. If all of them are NULL, the result is NULL.

SQL
SELECT
  COALESCE(NULL, NULL, 'third', 'fourth'),  -- third
  COALESCE('first', 'second'),              -- first
  COALESCE(NULL, NULL);                     -- NULL

The everyday use is a default value: show a column if it has a value, and something friendlier if it doesn't. Three employees in the company database have no email address:

SQL Default text for missing emails ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The original email column still shows NULL, while contact shows the fallback text. COALESCE doesn't change the stored data; it only changes what the query returns.

COALESCE is part of the SQL standard and works in every major database, which makes it the best default choice.

Fallback chains

With more than two arguments, COALESCE tries alternatives in order of preference. Imagine a customer table with mobile_phone, work_phone and home_phone columns; COALESCE(mobile_phone, work_phone, home_phone, 'no phone') picks the best available number for each row.

The company's projects table has a similar situation. Ongoing projects have no ends_on date yet, and a report wants to show either the end date or a label:

SQL Ongoing projects ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The second expression shows COALESCE inside a calculation: for ongoing projects, the duration is measured up to a report date (January 1, 2026) instead of becoming NULL. Without COALESCE, julianday(NULL) would be NULL and so would the whole subtraction. (The date functions are explained in Date and Time Functions.)

Defaults change meaning: be careful with 0

Replacing NULL with 0 looks harmless, but it changes what the data says. NULL means "unknown"; 0 means "we know it's zero". The Quarterly Forecasting project has no budget recorded. Compare the two averages:

SQL NULL versus zero in an average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

avg(budget) ignores the unknown budget and averages the six known ones: 217,500. Replacing NULL with 0 claims that one project has a budget of zero, which drags the average down to about 186,429. Neither number is "wrong", but they answer different questions. Use a numeric default only when zero really is the right value, such as "no discount" or "no items sold". Aggregate Functions covers how each aggregate treats NULL.

Filtering rows that contain NULL

A condition like homeroom <> 'A' looks as if it should return every student who isn't in homeroom A. But for students with no homeroom, NULL <> 'A' is unknown, and WHERE only keeps rows where the condition is true. Those students silently disappear:

SQL NULL rows vanish from WHERE ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The school has 36 students and 8 of them are in homeroom A, yet the second query returns only 24, not 28. The four students without a homeroom are missing. COALESCE turns their NULL into an empty string, which is definitely not 'A', so the third query counts them.

The more explicit alternative is WHERE homeroom <> 'A' OR homeroom IS NULL. Both are correct; the explicit version is often easier to read and lets the database use an index on homeroom. The Writing Fast Queries lesson explains that trade-off.

IFNULL, ISNULL and NVL: two-argument versions

Many databases have an older, two-argument function that does the same job as COALESCE(a, b):

Database Function Notes
SQLite ifnull(a, b) exactly COALESCE(a, b)
MySQL / MariaDB IFNULL(a, b) exactly COALESCE(a, b)
SQL Server ISNULL(a, b) result takes the type of a, which can cut off longer text
Oracle NVL(a, b) always evaluates b, even when a isn't NULL
PostgreSQL none use COALESCE

Every one of these also supports COALESCE, which accepts any number of arguments and behaves the same everywhere. Prefer COALESCE in new code; recognize the others when you read existing queries.

Watch the types. In SQLite, COALESCE(budget, 'unknown') returns a number for some rows and text for others, and SQLite allows it. PostgreSQL and SQL Server reject the mix, because every argument must have a compatible type. Convert first: COALESCE(CAST(budget AS TEXT), 'unknown'). The Types and CAST lesson explains CAST.

NULLIF: turning a value into NULL

NULLIF(a, b) returns NULL if a equals b, and returns a otherwise. It sounds odd at first ("why would I want a NULL?"), but it has two very practical jobs.

SQL
SELECT
  NULLIF(5, 5),          -- NULL
  NULLIF(5, 6),          -- 5
  NULLIF('', ''),        -- NULL
  NULLIF('N/A', 'n/a');  -- N/A (the comparison is case-sensitive)

Job 1: avoiding division by zero

Dividing by zero returns NULL in SQLite, but PostgreSQL and SQL Server stop the whole query with an error. x / NULLIF(y, 0) makes the divisor NULL when it's zero, and dividing by NULL quietly gives NULL in every database. It's the shortest portable guard there is.

This example builds a small table of ad campaigns, one of which got no clicks at all:

SQL Safe division with NULLIF

Press Run (or Ctrl + Enter) to see the result here.

The billboard's conversion rate is NULL instead of an error, and the label column wraps the whole thing in COALESCE to show no clicks. This works because || with a NULL piece returns NULL, which COALESCE then replaces. The last column shows a trap: printf() treats a NULL argument as 0, so it prints a misleading 0.0% for the billboard. Check for NULL before formatting. Notice the 100.0: it makes the division use decimals instead of integer division, as explained in Numeric Functions. The NULLIF + COALESCE pair is a pattern you'll write again and again.

Job 2: cleaning placeholder values

Imported data often uses fake values for "unknown": empty strings, 'N/A', 0 or -1. Those look like real data to SQL, so they're counted, averaged and sorted as if they were real. NULLIF converts them back to honest NULLs, and combining it with trim() also catches strings that contain only spaces:

SQL Clean up placeholder values

Press Run (or Ctrl + Enter) to see the result here.

The naive average includes the two -1 placeholders and comes out at 14.75; the real average of the two known ages is 30.5. The expression COALESCE(NULLIF(trim(city), ''), 'unknown') reads from the inside out: remove spaces, turn an empty result into NULL, then replace NULL with a label. It handles empty strings, blank strings and real NULLs in one go.

How COALESCE evaluates its arguments

COALESCE is really a shorthand for a CASE expression. COALESCE(a, b, c) means:

SQL
CASE
  WHEN a IS NOT NULL THEN a
  WHEN b IS NOT NULL THEN b
  ELSE c
END

Like CASE, it stops at the first non-NULL argument, so later arguments aren't evaluated. That matters when an argument is expensive, such as a subquery. Likewise, NULLIF(a, b) means CASE WHEN a = b THEN NULL ELSE a END. Knowing these equivalents helps when you need slightly different logic: just write the CASE yourself. See CASE Expressions.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does COALESCE(NULL, 0, 5) return?

    COALESCE returns the first argument that isn't NULL. 0 is a real value, not NULL, so the result is 0.
  2. 2. What does NULLIF(10, 10) return?

    NULLIF returns NULL when its two arguments are equal, otherwise the first argument.
  3. 3. Which expression divides total by qty without an error when qty is 0, in every database?

    NULLIF turns a zero divisor into NULL, and dividing by NULL returns NULL instead of an error. The other options still divide by zero.
  4. 4. A table has 10 rows; 3 have city = 'Lima' and 2 have city NULL. How many rows does WHERE city <> 'Lima' return?

    NULL <> 'Lima' is unknown, so the 2 NULL rows are dropped along with the 3 Lima rows. Use COALESCE(city, '') <> 'Lima' or add OR city IS NULL to include them.
  5. 5. Why can avg(COALESCE(budget, 0)) give a misleading answer?

    avg() normally ignores NULLs. Replacing them with 0 adds fake zero budgets to the calculation.

Summary

  • COALESCE(a, b, ...) returns the first non-NULL argument; use it for display defaults and fallback chains.
  • COALESCE only changes the query result, never the stored data, and it stops evaluating at the first non-NULL value.
  • Replacing NULL with 0 changes the meaning of averages and counts; use numeric defaults only when zero is truly correct.
  • Conditions such as x <> 'A' silently drop NULL rows; add OR x IS NULL or wrap the column in COALESCE.
  • IFNULL (SQLite, MySQL), ISNULL (SQL Server) and NVL (Oracle) are two-argument versions; COALESCE is standard and portable.
  • NULLIF(a, b) returns NULL when a = b: x / NULLIF(y, 0) prevents division-by-zero errors, and NULLIF(trim(col), '') cleans placeholder values.
  • Keep the types of all COALESCE arguments compatible, using CAST when needed.

Next: Types and CAST, where you'll see how SQLite stores values and convert between types.