SQL COALESCE: NULL, default values and misleading averages

A blank field in a report can look like an invitation to insert a zero. Before doing that, I check what the missing value means. Zero points might be a valid result, while a missing result might mean that nobody has taken the measurement yet. Once both become zero, telling them apart gets harder.

COALESCE lets you select a replacement value or the first available field. The syntax is short. Deciding when that replacement makes sense takes more thought. I will show you how to choose a display name, handle empty text, and compare two average calculations that look similar but answer different questions.

I checked every SQL block and result table below in SQLite 3.53.1 through node:sqlite in Node 24.18.0. Run them in order in one fresh test connection. Notes about PostgreSQL, SQL Server and MySQL come from their documentation; they do not claim that these examples were executed on those engines.

What COALESCE selects

COALESCE(a, b, c) returns the first non-NULL argument, reading from the left. If every argument is NULL, the result is NULL too. SQLite requires at least two arguments. The SQLite function documentation describes this rule. It does not mean the database assesses the quality of the selected text.

Start with a small query that needs no table:

-- example: basics
SELECT COALESCE(NULL, 'A', 'B') AS chosen,
       COALESCE(NULL, NULL) AS missing,
       COALESCE(0, 99) AS zero_value,
       LENGTH(COALESCE('', 'fallback')) AS text_length;

The first column contains A, even though B is also available further to the right. The second has no available value. In the third, zero stops the selection. The final column measures the length of the chosen text: zero means the empty string survived, and the word fallback was never selected.

This selects one value within the current row. It does not concatenate names, search the entire table for the first matching record, or find the most recent address. If you need a person’s full name, choosing either their first or last name will not construct it. Argument order is part of the rule behind your report.

A dataset with visible gaps

I will use five synthetic profiles. preferred_name holds the preferred display name, and fallback_name holds a backup. score is a measurement in points, while adjustment is a point adjustment. The names and numbers are invented. You do not need a download or any real personal data.

-- example: fixture
CREATE TEMP TABLE profiles (
  id INTEGER PRIMARY KEY,
  preferred_name TEXT,
  fallback_name TEXT,
  score INTEGER,
  adjustment INTEGER
);
INSERT INTO profiles VALUES
  (1, 'Ada', 'A', 10, 2),
  (2, NULL, 'Bea', NULL, 3),
  (3, '', 'Cara', 0, NULL),
  (4, '   ', 'Dan', 20, 0),
  (5, NULL, NULL, NULL, NULL);

Pay attention to the third and fourth rows. Cara has a measured score of zero and an empty preferred name. Dan has a score of twenty, but the preferred name contains three ordinary spaces. The fifth profile has neither a name nor a measurement. These differences will matter in the results.

The temporary table belongs to this test connection. The queries that follow only read it. Applying COALESCE in a SELECT does not store the replacement label in the column or remove the original missing values. That is useful: you can improve presentation while keeping the source values available for quality checks.

Preferred names, fallback names and empty text

My first rule is simple: take the preferred name, then the fallback name, then the label Anonymous. I use the same label in both language versions of this exercise. SQLite’s QUOTE function displays text as a SQL literal, making empty strings and spaces visible in the result table.

-- example: priority
SELECT id,
       QUOTE(COALESCE(preferred_name, fallback_name, 'Anonymous'))
         AS label_literal
FROM profiles
ORDER BY id;

Ada keeps the preferred name. Bea uses the backup. Cara and Dan still appear to have missing names, even though their fields are not NULL. Adding more arguments to COALESCE will not fix that: its first argument already contains a value.

For this report, I will make the rule explicit: an empty name, or one consisting entirely of ordinary spaces, counts as missing. First, TRIM removes spaces from the ends. Then NULLIF(..., '') converts an empty result to NULL. Only after that do I select a name:

-- example: clean
SELECT id,
       COALESCE(NULLIF(TRIM(preferred_name), ''),
                NULLIF(TRIM(fallback_name), ''),
                'Anonymous') AS display_name
FROM profiles
ORDER BY id;

The output now matches the stated rule. SQLite’s default TRIM removes ordinary space characters from the ends; it is not a universal cleaner for tabs, line breaks and every Unicode whitespace character. The SQLite documentation defines TRIM, NULLIF and QUOTE.

I would not automatically strip every whitespace character from any column. A display name, a password and a file identifier can follow different rules. I establish the meaning of the field before choosing a transformation. Otherwise, a cleaner-looking table can hide an import problem.

When CASE helps

You can express the same selection order more explicitly. Below, I compare a CASE expression with the simple COALESCE used before cleaning the text. In SQLite, one represents a true comparison.

-- example: cases
SELECT id,
       CASE
         WHEN preferred_name IS NOT NULL THEN preferred_name
         WHEN fallback_name IS NOT NULL THEN fallback_name
         ELSE 'Anonymous'
       END = COALESCE(preferred_name, fallback_name, 'Anonymous')
         AS same_result
FROM profiles
ORDER BY id;

Every result matches, including empty text and spaces. The CASE conditions test exactly what we need: whether the candidate value is not NULL. A condition unrelated to the field being selected would not make an equivalent comparison.

I use COALESCE when the decision depends only on availability. I reach for CASE when a separate condition matters, such as whether a profile has been verified. An address can exist without being eligible for a particular report. Presence alone does not settle that decision. The entry on SQL CASE WHEN explains conditional expressions in more detail.

Where to replace a missing value in arithmetic

For this exercise only, assume that a missing adjustment means zero adjustment, while a missing measurement still means an unknown score. Compare these three calculations:

-- example: arithmetic
SELECT id,
       score + adjustment AS raw_total,
       score + COALESCE(adjustment, 0) AS adjusted_total,
       COALESCE(score + adjustment, 0) AS defaulted_total
FROM profiles
ORDER BY id;

Cara’s raw total is unknown because the adjustment is NULL. Replacing only that adjustment with zero gives the valid result zero. Bea’s score remains unknown: we know the adjustment is three, but we do not know the starting score.

The final column replaces the entire unknown expression with zero. As a result, Bea and the profile with no measurement get the same value as Cara. Both expressions are valid SQL. Only the middle one implements our assumption that the adjustment can be filled in while the measurement must remain unknown.

I do not treat a default as a repair for every operation. COALESCE responds to a NULL result; it is not a general exception handler. An invalid type requires a type or input-validation fix. I also keep separate information about which values were originally missing.

Why an average can change from ten to six

Our three known measurements are ten, zero and twenty. Their sum is thirty. The average of the observed measurements is ten. If we replace two missing measurements with zeros, we divide the same numerator by five and get six.

-- example: averages
SELECT AVG(score) AS observed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean,
       COALESCE(AVG(score), 0) AS displayed_mean,
       COUNT(score) AS measured_rows,
       COUNT(*) AS all_rows
FROM profiles;

AVG(COALESCE(score, 0)) changes the aggregate’s input values. COALESCE(AVG(score), 0) changes its result only when that result is NULL. In this dataset, the ordinary average exists, so the outer COALESCE leaves ten unchanged. The SQLite aggregate documentation describes how AVG handles missing values.

To see the other situation, restrict the input to the two profiles with no measurement:

-- example: allnull
SELECT AVG(score) AS observed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean,
       COALESCE(AVG(score), 0) AS displayed_mean,
       COUNT(score) AS measured_rows,
       COUNT(*) AS all_rows
FROM profiles
WHERE id IN (2, 5);

The displayed zero in the third column does not prove that a measurement happened. That is why I included both a count of known values and a count of all rows. When a report is meant to assess data quality, those counts can be more useful than a convenient label. The SQL COUNT entry explains their meaning.

Having no input rows at all is another case:

-- example: empty
SELECT COALESCE(SUM(score), 0) AS displayed_sum,
       COALESCE(AVG(score), 0) AS displayed_mean,
       AVG(COALESCE(score, 0)) AS imputed_mean
FROM profiles
WHERE id < 0;

The inner replacement has no rows to process. An outer replacement can turn the aggregate’s NULL result into zero. This does not mean that the function creates missing groups:

-- example: groups
SELECT preferred_name,
       COALESCE(SUM(score), 0) AS displayed_sum
FROM profiles
WHERE id < 0
GROUP BY preferred_name;

That last table has headers and zero rows. No profile matches the filter, so no group is formed. A report that must show every month, including months without events, needs a list of months and an appropriate join. COALESCE alone will not create that list.

COALESCE, ISNULL and types across databases

I check the meaning before moving the name ISNULL between databases. In SQL Server, it takes two arguments and returns one value, substituting the second if the first is missing. In MySQL, it takes one argument and returns a missing-value test: one or zero. SQLite offers the two-argument IFNULL, described in its function documentation.

Result types differ too. PostgreSQL requires arguments that can be converted to a common type. SQL Server COALESCE uses type precedence, while ISNULL generally takes its type from the first argument, with an exception for a literal NULL. A replacement string can also be truncated.

For that reason, I keep a numeric measure numeric and put a missing-value description in a separate field. I do not assume that combining a number and the text 'Unknown' in one expression behaves identically in every database.

Microsoft also documents the possibility of repeated evaluation when a COALESCE argument contains a subquery. That is not an issue with the simple columns in this exercise. For expensive or changing expressions, check the rules of your database. I would not promise a faster query just because one function replaces another.

Exercise: show the name and where it came from

Build a result with a name cleaned according to the earlier rule. Add a field saying whether it came from the preferred name, the fallback, or the missing-name label. Separately flag missing measurements. A score of zero must remain an observed measurement.

Try writing it first, then compare your solution:

-- example: exercise
WITH cleaned AS (
  SELECT id, score,
         NULLIF(TRIM(preferred_name), '') AS preferred,
         NULLIF(TRIM(fallback_name), '') AS fallback
  FROM profiles
)
SELECT id,
       COALESCE(preferred, fallback, 'Anonymous') AS display_name,
       CASE
         WHEN preferred IS NOT NULL THEN 'preferred'
         WHEN fallback IS NOT NULL THEN 'fallback'
         ELSE 'missing'
       END AS name_source,
       CASE WHEN score IS NULL THEN 1 ELSE 0 END AS score_missing
FROM cleaned
ORDER BY id;

The cleaned common table expression names the cleaned fields so the rule does not have to be repeated in several places. COALESCE selects the label, while CASE describes its source. Profile five does not silently become a complete record just because the report can display it.

Finally, give Bea a valid preferred name in the test dataset. It should take priority over the fallback, and the source should change to preferred. Then make every score NULL and check the counts. A small test like this shows whether your default follows the intended rule and whether missing information remains visible.