In SQL, handling missing or NULL values is essential for reliable data analysis and application logic. The SQL COALESCE function provides a simple, standard way to return the first available (non-NULL) value from a list -making it a go-to tool for cleaning data, setting defaults, and building resilient queries across modern databases.
In this article, Lukas Vileikis explains exactly how the SQL COALESCE function works. Learn when, where, and why you should use it – plus when not to use it (and alternatives to consider.)
In the ever-evolving world of big data, one thing seems certain: data is a mess. Well, perhaps that’s not the fault of the data itself when it comes to missing values and incomplete records – for example, the latter is often the fault of developers, too. After all, developers are the people who insert, update, delete, and build upon, these records.
And regarding that final point – building upon data – developers often encounter numerous issues here. Issues that arise suddenly, and need to be urgently taken care of. One of these involves removing (or updating) NULL values, and that in turn is where the COALESCE function in SQL – the topic of this article – comes into play.
What is the COALESCE function in SQL?
When the need to deal with NULL values arises, multiple queries come onto the scene. The SQL COALESCE function is one of them. In simple terms, the SQL COALESCE function is a ‘fallback’ mechanism for missing data. Its only task is to return the first non-NULL value from a list of values.
How does the SQL COALESCE function work?
The SQL COALESCE function works as follows:
COALESCE function worksIn practical terms, here’s how to employ the SQL COALESCE function:
COALESCE(values);
Here, values refers to a comma-separated list of values that may or may not include NULL values. In the real world, everything comes with a SELECT clause, like so:
SELECT COALESCE(NULL, NULL, NULL, value1, NULL, value2, value3, value4, value5...);
Here, we pass a list of values to the COALESCE SQL function. The only task of that function is to return the first value that is not NULL.
So, in this case, the COALESCE SQL function would return “value1” as it is the first value in the list that is not NULL.
How (and where) should you use the SQL COALESCE function?
Now you know that the COALESCE SQL function returns the first non-NULL value in a list. Cool. But how does it work in the real world? How, why, and where, is it used?
The COALESCE function in SQL is used to assist in a variety of different tasks involving NULL values since, by using COALESCE, we are telling the database ‘if this is NULL, try the next option and keep going until the value is no longer NULL‘.
Fast, reliable and consistent SQL Server development…
Here’s some of what we can do with the SQL COALESCE function:
- Replace NULL with a default value (in this case, replace the first
NULLvalue in anfnamecolumn withN/A):SELECT COALESCE(fname, 'N/A') AS first_name FROM users;
- Use the
COALESCESQL function to updateNULLvalues. Here, we replace allNULLvalues in the city column with ‘Unknown’:UPDATE users SET city = COALESCE(city, 'Unknown');
- Handle missing values by effectively saying ‘if this value isn’t
NULL, use it. If not, use 1 instead’ , when performing statistics-related calculations:SELECT revenue / COALESCE(users, 1) AS revenue_formatted FROM stats;
- Use the
COALESCESQL function to perform sorting operations by pushingNULLvalues to the end of the result set. Here, we tell our database that if a deadline isNULL, it should be replaced with 2027-12-31, then perform anORDER BY:SELECT * FROM tasks ORDER BY COALESCE(deadline, '2027-12-31');
- Use the
COALESCESQL function to implement a fallback order. Here, we tell our database to useshipping_addressif it’s notNULL. If it isNULL, we can use a value derived frombilling_address. Finally, if both areNULL, specify the address as ‘No address’:SELECT COALESCE(shipping_address, billing_address, 'No address') FROM customers;
COALESCE SQL function
As you can see, the COALESCE SQL function is indeed multifaceted, and might actually be one of the most powerful SQL companions you’ll encounter. This is despite it looking quite basic at a first glance. COALESCE is simple and old – yet still very relevant.
When NOT to use the SQL COALESCE function
Take a look at your data once again. What do you see? Event streams? APIs with optional fields? Data lakes with inconsistent schemas? Pipelines for analytics with the ability to merge multiple sources?
In all of those scenarios, the SQL COALESCE function is of immense value. It acts as a stabilizer in an unstable world of data. Update NULL values, handle missing values, perform sorting operations, implement fallback logic, obtain a safe default value to use for calculations, sort with intent, and obtain a cleaner data set for reporting.
However, like everything in life, there are trade-offs to consider when using the SQL COALESCE function. Take the following query, for example:
SELECT COALESCE(users, 1);
This query would return ‘1’ once a value in a column is NULL – but what does that number signify? Is it NULL (unknown value) or ‘1’ (exactly one row?) You‘re now no longer dealing with a substitution.
Bottom line: in modern data systems (such as event streams and APIs), the SQL COALESCE function is a powerful tool for handling missing values, enforcing defaults, and stabilizing queries. But every substitution changes the semantics of your data. Use the SQL COALESCE function deliberately (not just to fill gaps, but to preserve meaning) – and make sure it’s right for your use case before implementation!
Alternatives to the SQL COALESCE function
When it comes to your specific use case, the SQL COALESCE function may not always be the correct one to use. While the function is standard SQL and can handle multiple arguments, sometimes you may want to look the other way. Some alternatives to the SQL COALESCE function are:
ISNULLfor SQL Server and Sybase taking two arguments.
NVLfor Oracle taking two arguments.
| Alternative | About | Example |
IFNULL | Returns the first value if it is not NULL. Otherwise, returns the second value. Only works with MySQL and SQLite. | SELECT IFNULL(user, 1) AS user_new FROM stats; |
ISNULL | Returns the first value if it is not NULL, otherwise returns the second value. Only works with SQL Server and Sybase. | SELECT ISNULL(city, “Unknown“) AS city_new FROM users; |
NVL | Returns first value if it is not NULL, otherwise returns the second one. Only works with Oracle. | SELECT NVL(salary, 0) AS salary_new |
Here are some of the key differences between the SQL COALESCE function and those detailed above:
| Function | Database | Notes |
COALESCE | All databases supporting standard SQL | Portable, standard-compliant, takes 2+ arguments |
IFNULL | MySQL, MariaDB, SQLite | Takes only 2 arguments, no more |
ISNULL | SQL Server | Takes only 2 arguments, no more |
NVL | Oracle | Takes only 2 arguments, no more |
All three of these queries would produce the same output – cool, right?
SELECT [IFNULL|ISNULL|NVL](email, “noemail@example.com“) AS contact_email FROM users;
IFNULL in MySQL Summary: why you should use the SQL COALESCE function
Contrary to popular belief, the SQL COALESCE function rarely walks alone. This powerful function sits comfortably in the middle of data integrity, usability, and business logic – helping applications and databases handle complex data with care.
The real strength of this function lies not only in filling gaps but also in making serious business decisions. While there are some replacements for the SQL COALESCE function, many DBAs rightfully choose it over its counterparts when business logic is at stake.
Simple Talk is brought to you by Redgate Software
FAQs: SQL COALESCE Function
1. What does the COALESCE function do in SQL?
The COALESCE function returns the first non-NULL value from a list of expressions, making it useful for handling missing or incomplete data.
2. When should I use COALESCE in SQL?
Use COALESCE when you need to replace NULL values, set default values, or create fallback logic in queries, calculations, or sorting.
3. What is the syntax of COALESCE?
The basic syntax is:
COALESCE(value1, value2, ..., valueN)
It evaluates each value in order and returns the first one that is not NULL.
4. Can COALESCE be used in SELECT and UPDATE statements?
Yes. COALESCE is commonly used in SELECT queries to display default values, and in UPDATE statements to replace NULL(s) in a table.
5. What’s the difference between COALESCE and ISNULL/IFNULL/NVL?
COALESCE is standard SQL and supports multiple arguments, while ISNULL, IFNULL, and NVL are database-specific (and typically accept only two arguments.)
6. Does COALESCE affect query performance?
In most cases, COALESCE has minimal performance impact, but excessive use in large queries or indexes can affect optimization depending on the database.
7. When should you NOT use the SQL COALESCE function?
Avoid using the SQL COALESCE function when replacing NULL(s) – it could change the meaning of your data, especially in analytics or reporting where NULL represents unknown values.
Load comments