COALESCE returns the first expression that is not NULL. In COALESCE(a, b, c), SQL checks a, then b, then c. If every expression is NULL, the result is NULL. This ordered fallback behavior is portable, but type conversion and evaluation details differ between database engines.
What COALESCE does
The basic syntax is:
COALESCE(expression_1, expression_2, expression_3)It accepts two or more expressions and returns the first one whose value is not NULL. The expressions can be columns, literals, calculations, function calls, or subqueries, subject to the type rules of your database.
SELECT COALESCE(description, short_description, '(none)') AS display_description
FROM products;For each row, this query uses description when it is populated. If that column is NULL, it tries short_description. If both are NULL, it returns the literal '(none)'. The placeholder changes only the query result; it does not write '(none)' into either stored column. PostgreSQL documents this fallback pattern in its conditional-expression reference (PostgreSQL 14 documentation).
| expression_1 | expression_2 | result |
|---|---|---|
| ‘Primary’ | ‘Backup’ | ‘Primary’ |
| NULL | ‘Backup’ | ‘Backup’ |
| NULL | NULL | NULL |
Adding a final non-null literal creates a guaranteed fallback:
#1 Best Overall
COALESCE(phone_mobile, phone_home, 'No phone number')That final value is a presentation or business-rule choice. It is not a special marker understood by SQL.
NULL is not the same as an empty value
NULL means “unknown” or “missing”; it is not equal to zero, FALSE, or an empty string. COALESCE reacts only to NULL. A text value of '' is therefore considered available on engines where it is a distinct empty string.
If your application treats blank text as missing, test that condition explicitly and verify the behavior for your engine. For example, a portable-looking pattern might use a conditional expression to turn a blank into NULL before calling COALESCE, but empty-string semantics are dialect-specific. Oracle, for example, documents its own character conversion rules; do not assume that a blank string behaves identically in every database.
Type resolution: make the fallback type intentional
COALESCE must produce one result type. The database decides whether the arguments can be converted to a common type and which type wins. Mixing text, numbers, dates, or database-specific types without an explicit cast can cause an error or an implicit conversion you did not intend.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →| Database and documentation | Relevant rule | Practical implication |
|---|---|---|
| PostgreSQL 14 | Arguments must be convertible to a common type. | Cast a fallback when the intended output type is not obvious. |
| Oracle Database 21 | Numeric arguments use numeric precedence and may be implicitly converted; COALESCE generalizes NVL. |
Keep numeric arguments compatible and avoid relying on broad implicit-conversion assumptions. |
| SQL Server | The result follows data-type precedence. If every argument is a NULL literal, at least one must be a typed NULL. |
Use CAST(NULL AS ...) when all-null branches are possible, and do not assume ISNULL has identical typing. |
| MySQL 8.0 | COALESCE is documented as a comparison/fallback function. | Check the 8.0 manual for the exact coercion behavior of mixed types in your expression. |
For a typed fallback, write the type explicitly when clarity or schema stability matters:
SELECT COALESCE(discount_amount, CAST(0.00 AS DECIMAL(10, 2))) AS discount_amount
FROM orders;The exact cast syntax and supported types depend on the engine. A date fallback should likewise be a date value, not an untyped string that the server must guess how to parse.
Evaluation order and side effects vary by engine
The semantic model is ordered fallback, but you should not make a universal claim that every argument is evaluated exactly once.
- Oracle Database 21: Oracle explicitly documents short-circuit evaluation for COALESCE (Oracle reference).
- PostgreSQL: PostgreSQL says only the arguments needed to determine the result are normally evaluated, while warning that planning can move expression evaluation and that the principle is not ironclad in every context (PostgreSQL 14 reference).
- SQL Server: Microsoft documents COALESCE as a rewrite to a
CASEexpression. That rewrite can evaluate an expression, including a subquery, more than once; concurrent changes can therefore affect the observed result (SQL Server documentation).
Do not put an expensive, volatile, or concurrency-sensitive subquery in a COALESCE branch and assume it is single-evaluation code. On SQL Server, Microsoft recommends stabilizing such a subquery in a subselect or choosing an isolation strategy appropriate to the consistency requirement. Test the behavior against the exact engine and version you deploy.
Useful COALESCE patterns
Choose the best available display field
SELECT
product_id,
COALESCE(description, short_description, '(none)') AS display_description
FROM products;This is appropriate when the fallback is for reporting or a user interface. It leaves the source data unchanged, so downstream code can still distinguish missing data from the placeholder.
Apply a business fallback to a calculation
Oracle’s documented example uses a discounted list price, then a minimum price, then a constant:
COALESCE(0.9 * list_price, min_price, 5)The first expression is used when it is not NULL; otherwise the next available price is selected. The literal 5 is merely an example of a business rule, not a universal pricing recommendation. Confirm the currency, precision, and type of every argument before using a similar expression.
Fallback across related columns
SELECT
customer_id,
COALESCE(preferred_email, billing_email, account_email) AS contact_email
FROM customers;The column order is a priority list. Put the most authoritative source first, not simply the column that is easiest to query.
Return NULL deliberately
COALESCE does not always need a non-null final literal:
SELECT COALESCE(work_phone, home_phone) AS phone
FROM employees;If both columns are missing, the result remains NULL. That is often preferable when consumers need to detect “no value” rather than display a made-up label.
COALESCE compared with CASE, ISNULL, and NVL
| Construct | Strength | Important qualification |
|---|---|---|
COALESCE |
Concise, ordered fallback syntax available across the reviewed engines. | Type resolution and evaluation details remain engine-specific. |
CASE |
Handles arbitrary predicates and multi-branch business logic. | Its typing and evaluation behavior still must be checked for the target engine. |
SQL Server ISNULL |
Two-argument SQL Server-specific alternative. | Microsoft documents differences from COALESCE, including data-type and nullability behavior; do not substitute it mechanically. |
Oracle NVL |
Oracle-specific two-argument fallback. | Oracle describes COALESCE as a generalization of NVL; portability is better with COALESCE when your supported engines agree. |
Use CASE when the choice depends on a condition rather than simply the first non-null value. Use a vendor function only when its engine-specific typing or behavior is part of the design.
Common errors and troubleshooting
“My query returns NULL”
Every argument evaluated to NULL. Add a final fallback if the result must never be null, or inspect each source column to find out why it is missing.
Free tools Windows power users keep installed
One-click scans. No signup required.
“The database reports incompatible types”
At least one argument cannot be converted to the common result type. Align the expressions or cast the fallback explicitly. Do not hide a data-quality problem by converting everything to text unless text is genuinely the required output.
“A SQL Server query with only NULL literals fails”
SQL Server requires at least one typed NULL when all arguments are NULL literals. For example:
SELECT COALESCE(CAST(NULL AS varchar(20)), CAST(NULL AS varchar(20)));“The fallback is not used for a blank string”
A blank string is not automatically NULL. Add an explicit blank test appropriate to your database, then pass the resulting value to COALESCE.
“A subquery seems to run twice on SQL Server”
That behavior is consistent with Microsoft’s documented CASE rewrite. Materialize the subquery in a subselect or use an isolation approach that provides the consistency your query needs; do not rely on COALESCE for single evaluation.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
“The result type or nullability changed after replacing ISNULL”
SQL Server treats ISNULL and COALESCE differently. Recheck casts, computed-column definitions, constraints, and client-side metadata after changing the expression.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Testing checklist before shipping a query
- Test a row where the first expression is non-null.
- Test rows where each earlier expression is null and a later one is populated.
- Test the all-null case.
- Test empty strings, zero, and
FALSEseparately if your application treats them as missing. - Verify the output type, precision, collation, and nullability in the target database.
- If a branch contains a subquery or volatile function, check the engine’s evaluation documentation and execution plan.
- Record the database product and version in migration or query documentation when conversion or evaluation behavior matters.
FAQ
Can COALESCE change data stored in a table?
No. In a SELECT expression it computes a value for that result set. Data changes only when you use the expression inside an INSERT, UPDATE, or other data-modification statement.
Does COALESCE treat zero as missing?
No. Zero is a non-null value and will be returned. Add an explicit condition if your business rule says zero should be ignored.
Can I pass more than three arguments?
Yes, where the engine supports the documented variadic form. Add arguments in priority order, and ensure all of them can resolve to a compatible result type.
Recommended Free Tools
Is COALESCE an aggregate function?
No. It operates on expressions within a row. Functions such as COUNT, MIN, and MAX aggregate across rows and have different null behavior.
Or skip the browser setup
If you need a clean screenshot of SQL documentation, a query result page, or an internal tool, ScreenshotNeo provides a website screenshot API and MCP server. Its capture flow accepts cookie or consent banners and removes more than 60 known consent platforms, newsletter popups, and chat widgets before the shot; each step can be disabled. Bot checks, CAPTCHAs, blank pages, timeouts, failed loads, and cache hits are not billed, and the response identifies the page verdict and billing status in headers.
A single request is enough:
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpSee the complete parameter list and response behavior in the ScreenshotNeo documentation. Equivalent 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)And 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}`);ScreenshotNeo also exposes an MCP server with 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 screenshots. Create a free ScreenshotNeo account.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →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.
