Understanding the COALESCE Function in SQL

COALESCE returns the first expression that is not NULL. It checks arguments from left to right. If every argument is NULL, the result is NULL.

COALESCE(expression_1, expression_2, expression_3)

For example:

SELECT COALESCE(description, short_description, '(none)') AS display_description
FROM products;

This query uses description when it has a value, falls back to short_description when it does not, and returns (none) only when both columns are NULL. The expression does not change either stored column; it changes the value returned by the query.

What COALESCE means

In SQL, NULL represents a missing, unknown, or inapplicable value. It is not the same as zero, an empty string, or a string containing spaces. Comparisons with NULL use three-valued logic, so column = NULL is not a test for missing data; use column IS NULL instead.

COALESCE is an ordered fallback expression. The database considers the first argument, then the second, and so on until it finds a non-NULL result.

Expression	Result
COALESCE('A', 'B')	'A'
COALESCE(NULL, 'B')	'B'
COALESCE(NULL, NULL, 10)	10
COALESCE(NULL, NULL)	NULL

PostgreSQL documents this first-non-NULL behavior and the all-NULL result in its conditional expressions documentation. Oracle describes the same rule for its Database 21 implementation, and MySQL includes equivalent examples in its 8.0 reference manual.

Basic patterns you can reuse

Choose a display label

SELECT
  product_id,
  COALESCE(name, sku, 'Unnamed product') AS label
FROM products;

This is useful in reports and exports where a blank result is less helpful than a clear placeholder.

Use a fallback value in a calculation

SELECT
  order_id,
  quantity * COALESCE(unit_price, 0) AS line_total
FROM order_items;

Here a missing price is treated as zero for this calculation. Make sure that business rule is correct: silently converting an unknown price to zero can hide data-quality problems.

Fallback across joined tables

SELECT
  u.id,
  COALESCE(p.display_name, u.email, 'Unknown user') AS contact_name
FROM users AS u
LEFT JOIN profiles AS p ON p.user_id = u.id;

A LEFT JOIN can produce NULL columns when no related row exists. COALESCE lets the result remain useful without changing the join.

Provide a date fallback

SELECT
  COALESCE(shipped_at, delivered_at, created_at) AS relevant_date
FROM orders;

Order matters. This expression prefers shipping time, then delivery time, then creation time. Reversing the arguments changes the meaning.

COALESCE with WHERE, ORDER BY, and GROUP BY

You can use COALESCE anywhere an expression is allowed.

Filtering

SELECT *
FROM tickets
WHERE COALESCE(priority, 'normal') = 'high';

This treats NULL priorities as normal, so missing priorities do not match high.

Sorting

SELECT *
FROM tasks
ORDER BY COALESCE(due_at, TIMESTAMP '9999-12-31') ASC;

The exact date-literal syntax varies by database. The example places tasks with no due date at the end. PostgreSQL also supports explicit NULLS FIRST and NULLS LAST, which may be clearer when you do not need a replacement value.

Grouping

SELECT
  COALESCE(region, 'Unassigned') AS region_name,
  COUNT(*) AS customer_count
FROM customers
GROUP BY COALESCE(region, 'Unassigned');

All NULL regions are grouped under the same label. The label is for the result set; the source data remains unchanged.

Type resolution and literal NULLs

Every argument must be compatible with the result type rules of your database. PostgreSQL requires arguments that can be converted to a common type. SQL Server chooses the highest-precedence type among the arguments. Oracle applies its documented numeric precedence and implicit conversion rules when the expressions are numeric or can be converted to numeric.

When the intended type is not obvious, cast the fallback explicitly:

SELECT COALESCE(discount_rate, CAST(0 AS DECIMAL(5,2)))
FROM products;

Explicit casts make migrations and code reviews safer, especially when a column may later change type.

SQL Server has a special rule for an expression made entirely of NULL literals: at least one NULL must be typed. This is valid:

SELECT COALESCE(CAST(NULL AS varchar(20)), CAST(NULL AS varchar(20)));

Do not assume that SQL Server ISNULL and COALESCE have identical return types or nullability metadata. Microsoft documents differences between the two functions in its COALESCE (Transact-SQL) reference.

Evaluation order and side effects

Portable SQL describes the logical result, but evaluation details are engine-specific.

• Oracle Database 21: Oracle documents short-circuit evaluation for COALESCE; it evaluates expressions from left to right and stops when a non-NULL value is found.
• PostgreSQL: PostgreSQL says only the arguments needed to determine the result are normally evaluated, but planning can move some expression evaluation to a different stage. Do not depend on short-circuiting to protect every constant expression or planner-foldable expression.
• SQL Server: Microsoft documents that COALESCE is rewritten as a CASE expression. A value expression containing a subquery can therefore be evaluated more than once, and concurrent changes can affect the result.

Avoid putting expensive, volatile, or side-effecting work directly inside a fallback chain. In SQL Server, stabilize a subquery in a subselect or apply the isolation guidance in Microsoft’s documentation when repeated evaluation matters.

COALESCE versus CASE, ISNULL, NVL, and IFNULL

Construct	Typical use	Important difference
COALESCE	Portable ordered fallback	Type and evaluation rules still depend on the engine
CASE	General conditional logic	More verbose but can express conditions beyond NULL fallback
SQL Server ISNULL	Two-argument replacement	Return type and nullability behavior differ from COALESCE
Oracle NVL	Two-argument replacement	Oracle describes COALESCE as a generalization of NVL
MySQL IFNULL	Two-argument replacement	Vendor-specific syntax; use COALESCE when portability matters

Use CASE when the condition is not simply NULL status:

SELECT CASE
         WHEN stock > 0 THEN 'In stock'
         WHEN stock IS NULL THEN 'Unknown'
         ELSE 'Out of stock'
       END AS availability
FROM products;

NULL, blank text, and zero are different

COALESCE(comment, 'No comment') replaces only NULL. It does not necessarily replace an empty string:

SELECT COALESCE(NULLIF(TRIM(comment), ''), 'No comment') AS visible_comment
FROM feedback;

TRIM and empty-string behavior differ across engines, so verify the expression for your dialect. Similarly, do not use COALESCE(amount, 0) unless zero is genuinely the correct interpretation of a missing amount.

Common mistakes and troubleshooting

The result is still NULL

All arguments evaluated to NULL. Add a final non-NULL fallback if the output must always contain a value, or inspect the source columns and joins.

A type-conversion error appears

The arguments cannot be converted to a common type, or an implicit conversion is attempting to parse invalid data. Cast each argument to the intended type and clean incompatible text values before calling COALESCE.

The wrong value wins

Arguments are ordered by priority, not by data quality. Put the preferred source first. Remember that an empty string may be non-NULL and therefore stop the chain.

A SQL Server subquery returns inconsistent data

The expression may be evaluated more than once because of SQL Server’s CASE rewrite. Materialize the subquery in a subselect or use an isolation level appropriate for the consistency requirement.

An index is no longer used

Wrapping an indexed column in a function inside a predicate can make a search non-sargable, depending on the optimizer. Compare the plan with an explicit NULL branch:

WHERE status = 'open' OR (status IS NULL AND 'open' = 'open')

The best rewrite is engine- and query-specific; inspect the execution plan before changing production SQL.

Performance, reliability, and maintainability

• Keep fallback expressions simple in large scans. Repeated functions, casts, or correlated subqueries can add CPU and I/O cost.
• Prefer a stored, normalized value when the same fallback is required by many reports and the business rule is stable.
• Indexing a generated or computed column containing the fallback can help, where your database supports it and the expression is deterministic.
• Use explicit casts at API boundaries so a driver receives a stable type.
• Test rows where every argument is NULL, only the first argument is populated, a later argument is populated, and values are blank or zero.
• Record the database engine and version in migrations and documentation. PostgreSQL 14, Oracle Database 21, SQL Server, and MySQL 8.0 can differ in conversion and evaluation details.

Or skip the browser setup

If you are documenting SQL examples with screenshots, you can capture a clean page with ScreenshotNeo instead of maintaining a browser automation stack. One GET request returns PNG, JPEG, WebP, or PDF output.

cURL:

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

Python:

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)

Node.js:

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

See the ScreenshotNeo API documentation for parameters and response details. Cookie banners, newsletter popups, and chat widgets are removed before the shot. Bot checks, blank pages, failed loads, timeouts, and cache hits are not billed, and response headers identify the page verdict and billing status. An MCP server provides take_screenshot, get_page_info, and capture_pdf tools for Claude, Cursor, and other MCP clients. The free plan includes 1,000 screenshots per month with no card; paid plans start at $5 for 3,000 shots. Create a free ScreenshotNeo account.

FAQ

Can COALESCE take only two arguments?

Yes. Two arguments are the common fallback form, and most reviewed engines also accept more. Oracle requires at least two expressions.

What happens when every argument is NULL?

The result is NULL. Add a final typed literal when the result must always be non-NULL.

Does COALESCE update the table?

No. It computes a value for the statement. Use an explicit UPDATE if you intend to store a replacement.

Is COALESCE portable?

The core syntax is widely supported, but common-type conversion, nullability metadata, and evaluation behavior require checking your database’s documentation.

Should I use COALESCE for validation?

Use it for result selection and defaults. Keep validation rules explicit so missing, blank, malformed, and zero values are not accidentally treated as the same state.