Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
COALESCE returns the first non-NULL value in an ordered list of expressions. If every expression is NULL, it returns NULL. It is a concise way to choose a fallback value, but the rules for compatible types and expression evaluation vary by database.
What does COALESCE do?
COALESCE(expression_1, expression_2, ...) checks expressions from left to right and returns the first one that is not NULL. If no expression provides a value, the result is NULL.
For example, this query displays a full description when present, otherwise a short description, and otherwise a presentation placeholder:
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →SELECT COALESCE(description, short_description, '(none)') AS display_description
FROM products;If description is non-NULL, that value wins. If it is NULL, SQL considers short_description; if that is also NULL, the result is '(none)'. The placeholder affects the query result only; it does not update either column.
#1 Best Overall
PostgreSQL 14 documents this ordered fallback behavior and example in its conditional expressions reference. Oracle Database 21 and SQL Server documentation describe the same basic purpose, and MySQL 8.0 also documents COALESCE.
How to write a COALESCE expression
The portable pattern is to supply at least two expressions, with the preferred value first and one or more fallbacks after it:
COALESCE(preferred_value, fallback_value, another_fallback)Argument order is meaningful: SQL does not search for the “best” value; it returns the first non-NULL expression. Put the most preferred candidate first. Each expression must also be acceptable under the database engine’s type-resolution rules.
Use a fallback for display
A fallback can make a query result more readable without changing stored data:
SELECT COALESCE(nickname, full_name, '(unnamed)') AS contact_name
FROM contacts;This is useful for result sets, reports, and output formatting. If downstream code needs to distinguish an actually stored value from a display placeholder, keep that distinction in mind: the selected result alone does not show which argument supplied it.
Use a fallback in a calculation
Oracle’s Database 21 documentation illustrates a price expression that uses a discounted list price when available, then a minimum price, then a constant:
COALESCE(0.9 * list_price, min_price, 5)The expression returns the first non-NULL result. The values and ordering are illustrative business logic, not a general pricing recommendation; choose fallbacks that match your application’s rules.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsWhat happens when every argument is NULL?
If all arguments evaluate to NULL, COALESCE returns NULL. A final non-NULL literal, such as '(none)' in the description example, changes that outcome by providing a fallback. Without one, the function does not invent a value.
SQL Server has an additional typing rule for an expression made entirely of NULL literals: at least one NULL must be explicitly typed. For example, use a cast to identify the intended type rather than relying on an untyped literal:
SELECT COALESCE(CAST(NULL AS varchar(20)), CAST(NULL AS varchar(20)));Choose a type that is valid for the database and the intended result. This requirement is documented in Microsoft’s SQL Server COALESCE reference.
How SQL engines resolve argument types
COALESCE returns one value, so the database must determine a result type for the expression. Do not assume that a fallback literal will be converted the same way in every engine—or that mixing values of different types will work without a conversion.
PostgreSQL
PostgreSQL requires the arguments to be convertible to a common type, which becomes the expression’s result type. If the intended conversion is not obvious, cast the relevant expression explicitly. See the PostgreSQL 14 documentation for its common-type rule.
SQL Server
SQL Server chooses the argument type with the highest type precedence. This can affect both implicit conversion and the result type, so check the precedence rules if the arguments are mixed. Microsoft also notes that COALESCE and ISNULL can differ in their return-type behavior; replacing one with the other is not automatically equivalent.
Oracle
Oracle Database 21 documents numeric precedence and implicit conversion when the arguments are numeric or can be implicitly converted to numeric. That is not a guarantee that arbitrary combinations of types convert identically. Use explicit conversions where the intended result type needs to be unambiguous. Oracle describes COALESCE as a generalization of NVL.
Practical type checks
- Confirm the result type rules for the database and version that will run the query.
- When combining numbers, strings, dates, or database-specific types, cast values explicitly if the intended type is not clear.
- For all-NULL literals in SQL Server, include a typed NULL.
- Test the query with representative values, including cases where the first candidate is NULL and a later one supplies the result.
COALESCE and expression evaluation
The result is an ordered fallback, but details about evaluating expressions should not be generalized across engines. This matters most when an argument contains a subquery, a costly operation, or a value that may change while a query is running.
Oracle Database 21
Oracle explicitly documents short-circuit evaluation for COALESCE: later expressions need not be evaluated after a non-NULL result is found. Refer to the Oracle Database 21 SQL Language Reference for the engine-specific guarantee.
PostgreSQL 14
PostgreSQL says only the arguments needed to determine the result are evaluated, but also cautions that expression evaluation can occur at different stages, such as during planning. Its short-circuit principle is not ironclad for every planning context. See the notes in the PostgreSQL conditional expressions reference.
SQL Server
SQL Server documents that COALESCE is rewritten as a CASE expression and that inputs can be evaluated multiple times. A subquery in an argument may run more than once, and under some isolation or concurrency conditions the repeated evaluation can produce different results. If a subquery must be evaluated once or consistently, follow Microsoft’s documented guidance for stabilizing the subquery, such as placing it in a subselect, or use an appropriate isolation strategy. Do not rely on a single-evaluation assumption for SQL Server.
These distinctions are why examples involving conversions or evaluation guarantees should name their database engine and version rather than promising identical behavior everywhere.
Recommended Free Tools
COALESCE does not automatically treat blank text as missing
The examples above concern SQL NULL, not every value a person might consider “empty.” An empty string, whitespace, or a placeholder is not automatically equivalent to NULL in every database. If blank text should also count as missing, use an explicit expression that tests or transforms blank values according to your engine’s semantics before applying the fallback. Verify that behavior for the database you use rather than assuming a universal rule.
Common mistakes and fixes
Expecting a later argument to override an earlier value
Symptom: the result uses a value you consider less useful. Cause: that value appeared first and was not NULL. Fix: reorder arguments so the preferred candidate comes first, or add a condition that defines what “useful” means.
Mixing incompatible types
Symptom: a type error, unexpected conversion, or surprising output type. Cause: the engine must resolve a common result type or apply its own precedence rules. Fix: check the target engine’s documentation and cast arguments to the intended type explicitly.
Using COALESCE to fix the stored data
Symptom: a report displays a placeholder, but the underlying column is still NULL. Cause: COALESCE changes the expression result, not the stored row. Fix: use an appropriate data-modification statement only if the goal is to update stored values; keep query-time presentation separate from data cleanup.
Free tools Windows power users keep installed
One-click scans. No signup required.
Assuming a SQL Server subquery runs once
Symptom: repeated work or results that can vary for an argument containing a subquery. Cause: SQL Server documents possible repeated evaluation of COALESCE inputs. Fix: use Microsoft’s documented approach for stabilizing the subquery or choose an appropriate isolation strategy.
Expecting blank strings to trigger the next argument
Symptom: an apparently empty value is returned instead of a fallback. Cause: the value is not NULL under that engine’s semantics. Fix: handle blank values explicitly, using a dialect-appropriate test or conversion.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.COALESCE versus CASE and vendor-specific functions
Use COALESCE when the rule is simply “take the first non-NULL expression.” It is compact and documented by the reviewed PostgreSQL, Oracle, SQL Server, and MySQL references. A CASE expression is more suitable when the choice depends on conditions other than whether a value is NULL.
Engines also provide alternatives such as SQL Server’s ISNULL and Oracle’s NVL. They have their own type and evaluation behavior: Oracle describes COALESCE as a generalization of NVL, while Microsoft warns that ISNULL and COALESCE can produce different type behavior. Choose based on the target database’s documentation and the exact semantics needed, not just on superficial similarity.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Screenshot alternative for a different kind of fallback
COALESCE selects values in SQL; it does not capture a web page. If your task is to get a website screenshot from code, ScreenshotNeo is a separate website screenshot API and MCP server for developers. A single GET request can return a PNG, JPEG, WebP, or PDF, and its documentation is at ScreenshotNeo.
Or skip the browser setup
For example, this cURL request captures Stripe as a WebP file. See the ScreenshotNeo API documentation for options and request details.
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpScreenshotNeo accepts cookie and consent banners like a visitor and removes more than 60 known consent platforms, newsletter popups, and chat widgets before capture; each cleanup step can be turned off. Bot checks, blank pages, timeouts, failed loads, and cache hits cost nothing, with response headers indicating the page verdict and billing status. Its MCP server provides take_screenshot, get_page_info, and capture_pdf tools for AI agents. The Free plan includes 1,000 screenshots a month without a card; paid plans start at $5 for 3,000 screenshots.
Sign up for ScreenshotNeo’s free plan to get 1,000 screenshots a month with no card.
Official references
- PostgreSQL 14: Conditional Expressions
- Oracle Database 21: COALESCE
- Microsoft SQL Server: COALESCE (Transact-SQL)
- MySQL 8.0: Comparison Functions and Operators
Frequently Asked Questions
Can COALESCE have only one argument?
The reviewed Oracle Database 21 reference requires at least two expressions. Use at least two arguments for the portable fallback pattern described here.
Does COALESCE change a NULL value in the table?
No. It returns a value for the query expression; it does not update the stored column.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
