MariaDB COALESCE(): Replace NULL Values with Examples
Learn MariaDB COALESCE() syntax, first non-NULL results, fallback values, COALESCE vs IFNULL, and the difference between ignoring NULL and replacing it with zero.
On this page
MariaDB COALESCE() returns the first non-NULL value in a list of expressions. It is useful for displaying a fallback value, selecting a preferred contact field, or handling aggregates that return NULL when no matching rows exist.
Syntax
COALESCE(value1, value2, ...)
Pass at least one expression. MariaDB evaluates the expressions to find the first value that is not SQL NULL. If every expression is NULL, the result is NULL. In a typical query, the expressions should have compatible intended meanings and types; do not assume that the result’s SQL type is always exactly the type of the first non-NULL input. MariaDB’s type handling also depends on expression context.
Examples
Return the first non-NULL value
SELECT
COALESCE(NULL, NULL, 'backup') AS preferred_value,
COALESCE(NULL, NULL) IS NULL AS all_missing;
preferred_value all_missing
--------------- -----------
backup 1The function skips the first two NULL arguments and returns 'backup'. If all arguments are SQL NULL, the expression returns NULL.
Display a fallback for a missing description
SELECT
name,
COALESCE(description, 'No description') AS display_description
FROM products;
This example assumes products(name, description) exists. It changes what the query displays, not the contents of the table. An empty string is not NULL, so COALESCE('', 'No description') returns an empty string.
If an empty string should also count as missing, you can use NULLIF():
SELECT COALESCE(NULLIF('', ''), 'No description') AS description;
description
--------------
No descriptionChoose among multiple contact fields
SELECT
customer_id,
COALESCE(work_email, personal_email, 'Not provided') AS contact_email
FROM customers;
This chooses the first populated email field for each customer. It does not validate that an email address is correct, and it will not skip an empty string unless you explicitly convert it to NULL first.
Do not silently change the denominator of AVG()
When aggregating data, AVG(price) ignores SQL NULL values. Replacing them with zero changes the meaning of the average:
SELECT
AVG(price) AS average_known_price,
AVG(COALESCE(price, 0)) AS average_with_missing_as_zero
FROM (
SELECT 10 AS price
UNION ALL SELECT 20
UNION ALL SELECT NULL
) AS prices;
The first average is 15, calculated from the two known prices. The second is 10, calculated from three rows after treating the missing price as zero. Both calculations can be valid, but they answer different questions.
For a display-only fallback when an aggregate has no non-NULL values, apply COALESCE() outside the aggregate instead:
SELECT COALESCE(SUM(price), 0) AS total_price
FROM products
WHERE 1 = 0;
total_price
-----------
0COALESCE() versus IFNULL()
| Function | Number of arguments | Behavior |
|---|---|---|
COALESCE(a, b, c) |
One or more | First non-NULL value |
IFNULL(a, b) |
Exactly two | a when not NULL, otherwise b |
With two arguments, both offer the same fallback behavior. Choose COALESCE() when you have several possible values, and IFNULL() when you want a two-way fallback. For more examples, see MariaDB IFNULL().
Common mistakes
Does COALESCE replace empty strings? No. Only SQL NULL values are skipped. Use NULLIF(expression, '') if an empty string should be treated as missing.
Does COALESCE modify the table? No. It computes an expression in a query; updating stored values requires an UPDATE statement.
Should I put COALESCE(column, 0) in every WHERE condition? Not automatically. Doing so changes the meaning of missing data and may prevent a normal index on the column from being used as effectively. Decide on the desired NULL behavior first and check the query plan for performance-critical statements.
For the authoritative function semantics and type-handling examples, see the official MariaDB COALESCE documentation.