CodeRang
SQL topics

SQL COALESCE for NULL Handling

SQL COALESCE returns the first non-NULL value from a list of expressions and is one of the core tools for handling missing values. This example also shows IS NULL semantics, aggregate behavior, and NULLIF.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • Test missing values with IS NULL
  • Provide fallbacks with COALESCE
  • Understand NULL in comparisons and aggregates
  • Use NULLIF for safe division
  • Handle NULL values produced by LEFT JOIN

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Calculate payments and credit-limit usage

For every account, return owner_name, total_paid, and limit_used_pct. If an account has no payments or all payment amounts are NULL, total_paid must be 0. Calculate limit usage as a percentage of credit_limit, but return NULL when the limit is zero.

Query 1PostgreSQLv16
SELECT a.owner_name,
       COALESCE(SUM(p.amount), 0) AS total_paid,
       ROUND(COALESCE(SUM(p.amount), 0) / NULLIF(a.credit_limit, 0) * 100, 1) AS limit_used_pct
FROM accounts a
LEFT JOIN payments p ON p.account_id = a.id
GROUP BY a.id, a.owner_name, a.credit_limit
ORDER BY total_paid DESC, a.owner_name;
Submit
Resultaccountspayments
Answer accepted

How SQL COALESCE and NULL handling work

LEFT JOIN keeps every account even when payments has no matching row. Olivia therefore remains in the result, while all columns coming from payments are NULL. There are also genuine NULL amounts stored in individual payment rows. SUM ignores those individual missing values, but when there is no known amount to aggregate at all, SUM returns NULL rather than zero.

COALESCE(SUM(p.amount), 0) turns that aggregate NULL into a numeric zero because the exercise defines both situations that way. COALESCE is more general than this example: it returns the first non-NULL expression from its argument list, so it can provide fallbacks for text, dates, identifiers, or other compatible values as well.

Noah introduces a different issue because credit_limit is zero. Dividing by it would raise an error. NULLIF(a.credit_limit, 0) returns NULL when the two arguments are equal, so the division produces NULL instead of failing. This also illustrates SQL's three-valued logic: NULL represents an unknown value, which is why comparisons such as amount = NULL do not behave like normal equality checks.

COALESCE evaluates its arguments from left to right and returns the first value that is not NULL. For example, COALESCE(total, 0) replaces a missing total with zero, but that replacement should only be used when zero has the correct business meaning. NULL itself is not equal to zero or an empty string, and comparisons such as column = NULL do not work as ordinary equality checks. Use IS NULL or IS NOT NULL instead. Aggregate functions such as SUM and AVG generally ignore individual NULL values, although an aggregate over no non-NULL values can still return NULL.

SQLPostgreSQL v16
SELECT a.owner_name,
       COALESCE(SUM(p.amount), 0) AS total_paid,
       ROUND(COALESCE(SUM(p.amount), 0) / NULLIF(a.credit_limit, 0) * 100, 1) AS limit_used_pct
FROM accounts a
LEFT JOIN payments p ON p.account_id = a.id
GROUP BY a.id, a.owner_name, a.credit_limit
ORDER BY total_paid DESC, a.owner_name;
Query result
owner_nametotal_paidlimit_used_pct
Mia200.0040.0
Paul175.0070.0
Noah100.00NULL
Olivia00.0
Rita00.0

Common SQL COALESCE and NULL mistakes

01

Writing = NULL

amount = NULL does not become true when amount is missing. A normal comparison with NULL evaluates to UNKNOWN. Use IS NULL to find missing values and IS NOT NULL to find known ones.

02

Assuming SUM returns zero

SUM skips individual NULL inputs, but an aggregate with no known numeric value returns NULL. If the required output is zero, make that rule explicit with COALESCE rather than relying on the aggregate.

03

Replacing every NULL with zero

A missing value and a confirmed value of zero can represent different facts. Apply COALESCE only when the domain rule allows that substitution. Otherwise you may erase information that later logic needs.

Practice problems on this topic

SQL from scratch course

Good SQL NULL practice should vary why the value is missing. One free SQL Arena exercise removes reviews without ratings while replacing missing comments with a fallback. Another asks for customers who never ordered, and a pages-with-no-likes exercise exposes NULL through an unmatched relationship. These require different choices between IS NULL, COALESCE, and join logic instead of repeating the same function. More advanced exercises deal with missing parameters, invalid or absent phone values, and typed data after ingestion. SQL Arena contains 1,192 exercises for PostgreSQL, MySQL, ClickHouse, and BigQuery. Solutions are checked against hidden data, so a query that accidentally works on the visible rows is not enough. The free plan requires no card and includes 292 exercises across all four dialects. For a structured path through NULL, expressions, filtering, and the surrounding SQL fundamentals, continue with the SQL from Scratch course. Every lesson includes a task, and the first chapters are free.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL COALESCE?

COALESCE is a SQL function that returns the first non-NULL expression from its argument list. It is commonly used to provide a fallback value when data is missing.

How does COALESCE work in SQL?

COALESCE evaluates arguments from left to right. It returns the first argument that is not NULL, or NULL if every argument is NULL.

What is the difference between COALESCE and IS NULL?

IS NULL checks whether a value is NULL and returns a boolean result. COALESCE returns a replacement or fallback value by selecting the first non-NULL argument.

Can I use COALESCE with SUM in SQL?

Yes. COALESCE(SUM(amount), 0) is often used when a SUM may return NULL and the desired output for that case is zero.

Why does = NULL not work in SQL?

NULL represents an unknown value, so ordinary equality comparison does not evaluate it as equal to another value. Use IS NULL or IS NOT NULL to test for NULL.

See also

Practice SQL COALESCE and NULL queries

Use SQL Arena to work through missing values caused by stored data, aggregates, calculations, and unmatched joins. Queries run in the browser and are checked against hidden data, with ER diagrams, EXPLAIN, and AI mentor hints available when needed.

Practice SQL NULL

More practice from CodeRang