COALESCE in MySQL
MySQL COALESCE is a built-in function that returns the first non-NULL value from an ordered list of arguments, and it accepts up to 255 arguments per call.
When a column evaluates to NULL, COALESCE steps to the next argument and returns that value instead, which makes it the standard way to substitute a fallback in SELECT results. The function was part of the SQL-92 standard and has shipped in every MySQL release from 3.23 onward, so any server you meet in production supports it without a plugin or a version flag.
COALESCE is not an exotic feature; it is the daily-tool answer to the NULL problem that surfaces in every reporting query. You will reach for it the same way you reach for GROUP BY or a JOIN. For a broader look at how these pieces fit together, see MySQL in practice for the surrounding ecosystem, a MySQL Workbench walkthrough for running and editing the queries covered here, Choosing between MySQL and PostgreSQL for the dialect differences that matter when you port a COALESCE-heavy schema, and Kill a Process on Linux for the operational side when a runaway query locks a table for minutes.
COALESCE syntax and how the function evaluates arguments
COALESCE follows a fixed evaluation order: it scans its argument list from left to right and returns the first value that is not NULL. Write the call as COALESCE(arg1, arg2, arg3, ...) and the function hands back arg1 the moment arg1 is non-NULL, skipping every argument to its right. If every argument is NULL, the function itself returns NULL. MySQL stops scanning as soon as it finds a match, so a 255-argument call that resolves on argument 2 does 2 comparisons, not 255. The function accepts any expression, not just bare columns: you can pass a CASE result, a subquery, a literal, or another function call as an argument, and COALESCE treats the evaluated result as its input.
Multi-argument fallbacks for layered defaults
With multiple arguments you build a priority chain, and each argument represents one fallback level. A typical pattern for an order total is COALESCE(coupon_value, tax_refund, 0), which says: use the coupon value if it exists, otherwise the tax refund, otherwise the integer 0. The leftmost argument carries the highest priority, so place the most specific source first and the broadest default last. Three fallback levels cover most reporting needs, but you can chain more. The following table shows how 3 common fallback patterns resolve for 2 sample rows:
| Expression | Row A (coupon = NULL, refund = 5.00) | Row B (coupon = 12.75, refund = 3.25) |
|---|---|---|
| COALESCE(coupon, refund, 0) | 5.00 | 12.75 |
| COALESCE(refund, coupon, 0) | 5.00 | 3.25 |
| COALESCE(coupon, 'none', 0) | 'none' | 12.75 |
The table makes one point clear: argument order changes the answer even when the same 3 values are present. The function does not sort or rank; it walks the list in the order you typed it.
COALESCE versus IFNULL in MySQL
IFNULL is the two-argument shorthand that COALESCE generalizes. IFNULL(expr, fallback) returns expr when it is non-NULL and fallback when it is NULL, and that is the entire contract. COALESCE does the same thing with 2 or more arguments and additionally follows the SQL standard, so a COALESCE call moves unchanged into PostgreSQL, MariaDB, or SQL Server, while IFNULL is a MySQL (and MariaDB) extension that PostgreSQL does not recognise. In performance terms, both functions short-circuit identically: MySQL evaluates arguments left to right and stops at the first non-NULL hit, so a COALESCE with 5 arguments that resolves on the 3rd costs the same as an IFNULL chain of 3 nested calls. Use IFNULL when you need exactly 1 fallback and are certain the query stays on MySQL; use COALESCE when the fallback list has 2 or more levels or when the query must port across dialects.
COALESCE in real queries: practical examples
The following examples show COALESCE inside queries you will actually run. Each one pulls from a small e-commerce schema with customers, orders, and order_items tables holding roughly 200,000 rows across 3 tables.
- Display a customer display name: SELECT COALESCE(middle_name, 'N/A') AS display_mid FROM customers; replaces the 412 NULL middle_name entries with the text 'N/A' so the report grid never shows a blank cell.
- Compute a net price: SELECT COALESCE(discount_pct, 0) * unit_price AS net FROM order_items; treats a missing discount as a 0 percent reduction, which is the correct default for 18,400 rows where discount_pct is NULL.
- Chain 3 sources for a contact: SELECT COALESCE(work_email, home_email, 'no-email') AS contact FROM customers; gives a usable address for every row, falling through 2 levels before the literal catch-all.
- Guard an aggregate: SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 4821; returns 0 instead of NULL when the customer has placed 0 orders, so the application layer does not have to null-check the total.
Every one of these queries runs as a single pass over the relevant index; COALESCE adds no extra scan and no temporary table. The optimizer treats the function as a row-level expression and folds it into the projection list, so a COALESCE on an indexed column does not prevent index usage the way a function on the search side of a WHERE clause would.
COALESCE with aggregates, GROUP BY, and JOINs
COALESCE interacts with aggregates in 2 specific ways that trip up new writers. First, an aggregate such as SUM or AVG returns NULL when the input set is empty, so wrapping the aggregate in COALESCE gives you a numeric floor: COALESCE(AVG(salary), 0) over an empty department group yields 0 rather than a NULL that breaks a downstream chart. Second, in a LEFT JOIN the right-hand columns are NULL for every unmatched row, and COALESCE is the idiomatic way to label those rows: COALESCE(region.name, 'Unassigned') tells the reader which 847 customer rows matched no region instead of leaving the column blank. MySQL does not evaluate COALESCE inside the GROUP BY key itself; it only applies in the SELECT list, HAVING, or WHERE after the grouping step, so place the function in the projection, not in the grouping expression, if you want a stable result across 8.0.x point releases.
COALESCE performance and common pitfalls
COALESCE is a CPU-bound, row-level function and its cost is proportional to the number of arguments you pass, not to the number of rows. A 4-argument COALESCE over a 500,000-row table adds a few milliseconds to an otherwise index-driven scan, which is negligible next to the I/O of reading 500,000 rows. The real performance cost shows up in 3 avoidable mistakes: nesting COALESCE inside a subquery that re-executes per row instead of using a JOIN; wrapping an indexed column in COALESCE on the search side of a WHERE clause, which defeats the index because MySQL cannot apply an index lookup through a function; and passing a correlated subquery as an argument, which turns a row-level call into a nested loop of its own. None of these mistakes is a COALESCE bug; they are query-shape problems that the function simply inherits. Keep the function in the SELECT projection or in a derived column, let the index do its job, and the 255-argument ceiling is never the limiting factor in a well-shaped query.