{"id":110277,"date":"2026-05-07T12:00:00","date_gmt":"2026-05-07T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110277"},"modified":"2026-05-05T14:31:04","modified_gmt":"2026-05-05T14:31:04","slug":"sql-coalesce-explained-how-to-handle-null-values-easily","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-coalesce-explained-how-to-handle-null-values-easily\/","title":{"rendered":"Why COALESCE might be the most useful SQL function you\u2019re not using right"},"content":{"rendered":"\n

In SQL, handling missing or NULL<\/code> values is essential for reliable data analysis and application logic. The SQL COALESCE<\/code> function provides a simple, standard way to return the first available (non-NULL<\/code>) value from a list -making it a go-to tool for cleaning data, setting defaults, and building resilient queries across modern databases.<\/strong> <\/p>\n\n\n\n

In this article, Lukas Vileikis explains exactly how the SQL COALESCE<\/code> function works. Learn when, where, and why<\/em> you should use it – plus when not<\/em> to use it (and alternatives to consider.<\/strong>)<\/p>\n\n\n\n

In the ever-evolving world of big data, one thing seems certain: data is a mess<\/a>. Well, perhaps that\u2019s 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.<\/p>\n\n\n\n

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<\/code> values<\/a>, and that in turn is where the COALESCE<\/code> function in SQL<\/a> – the topic of this article – comes into play.<\/p>\n\n\n\n

What is the COALESCE function in SQL? <\/h2>\n\n\n\n

When the need to deal with NULL<\/code> values arises, multiple queries come onto the scene. The SQL COALESCE<\/code> function is one of them. In simple terms, the SQL COALESCE<\/code> function is a ‘fallback’ mechanism for missing data. Its only task is to return the first non-NULL<\/code> value from a list of values. <\/strong><\/p>\n\n\n\n

How does the SQL COALESCE function work?<\/h2>\n\n\n\n

The SQL COALESCE function works as follows:<\/strong><\/p>\n\n\n\n

\"An
How the SQL COALESCE<\/code> function works<\/em><\/figcaption><\/figure>\n\n\n\n

In practical terms, here’s how to employ the SQL COALESCE<\/code> function:<\/strong><\/p>\n\n\n\n

COALESCE(values); <\/code><\/p>\n\n\n\n

Here, values refers to a comma-separated list of values that may or may not include NULL<\/code> values. In the real world, everything comes with a SELECT<\/a><\/code> clause, like so:<\/p>\n\n\n\n

SELECT COALESCE(NULL, NULL, NULL, value1, NULL, value2, value3, value4, value5...); <\/code><\/p>\n\n\n\n

Here, we pass a list of values to the COALESCE<\/code> SQL function. The only task of that function is to return the first value that is not<\/em> NULL<\/code>. <\/p>\n\n\n\n

So, in this case, the COALESCE<\/code> SQL function would return \u201cvalue1\u201d as it is the first value in the list that is not<\/em> NULL<\/code>. <\/p>\n\n\n\n

How (and where) should you use the SQL COALESCE function?<\/h2>\n\n\n\n

Now you know that the COALESCE<\/code> SQL function returns the first non-NULL<\/code> value in a list. Cool. But how does it work in the real world? How, why, and where, is it used?<\/p>\n\n\n\n

The COALESCE<\/code> function in SQL is used to assist in a variety of different tasks involving NULL<\/code> values since, by using COALESCE<\/code>, we are telling the database ‘if this is NULL<\/code>, try the next option and keep going until the value is no longer NULL<\/code>‘.<\/em><\/strong><\/p>\n\n\n\n

\n
\n
\n
\n

Fast, reliable and consistent SQL Server development…<\/h3>\n
\n …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows. <\/div>\n <\/div>\n Learn more & try for free<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

<\/p>\n\n\n\n

Here’s some of what we can do with the SQL COALESCE<\/code> function:<\/p>\n\n\n

\n
    \n
  1. Replace NULL with a default value<\/strong> (in this case, replace the first NULL<\/code> value in an fname<\/code> column with N\/A<\/code>): 
    SELECT COALESCE<\/strong>(fname, 'N\/A') AS first_name FROM users; <\/code><\/li>\n<\/ol>\n<\/div>\n\n
    \n
      \n
    1. Use the COALESCE <\/code>SQL function to update NULL<\/code> values.<\/strong> Here, we replace all NULL<\/code> values in the city column with ‘Unknown’: 
      UPDATE users SET city = COALESCE<\/strong>(city, 'Unknown'); <\/code><\/li>\n<\/ol>\n<\/div>\n\n
      \n
        \n
      1. Handle missing values<\/strong> by effectively saying ‘if this value isn\u2019t NULL<\/code>, use it. If not, use 1 instead’ <\/em>, when performing statistics-related calculations: 
        SELECT revenue \/ COALESCE<\/strong>(users, 1) AS revenue_formatted FROM stats; <\/code><\/li>\n<\/ol>\n<\/div>\n\n
        \n
          \n
        1. Use the COALESCE <\/code>SQL function to perform sorting operations<\/strong> by pushing NULL<\/code> values to the end of the result set. Here, we tell our database that if a deadline is NULL<\/code>, it should be replaced with 2027-12-31<\/em>, then perform an ORDER BY<\/code>: 
          SELECT * FROM tasks ORDER BY COALESCE<\/strong>(deadline, '2027-12-31'); <\/code><\/li>\n<\/ol>\n<\/div>\n\n
          \n
            \n
          1. Use the COALESCE<\/code> SQL function to implement a fallback order.<\/strong> Here, we tell our database to use shipping_address <\/code>if it\u2019s not NULL<\/code>. If it is<\/em> NULL<\/code>, we can use a value derived from billing_address<\/code>. Finally, if both<\/em> are NULL<\/code>, specify the address as ‘No address’: 
            SELECT COALESCE<\/strong>(shipping_address, billing_address, 'No address') FROM customers; <\/code><\/li>\n<\/ol>\n<\/div>\n\n\n
            \"an
            Returning addresses with the COALESCE<\/code> SQL function<\/em><\/figcaption><\/figure>\n\n\n\n
            \"an
            Updating the Users table<\/em><\/figcaption><\/figure>\n\n\n\n
            \"an
            An updated Users table<\/em><\/figcaption><\/figure>\n\n\n\n

            As you can see, the COALESCE<\/code> 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<\/code> is simple and old – yet still very<\/em> relevant. <\/strong><\/p>\n\n\n\n

            When NOT to use the SQL COALESCE function  <\/h2>\n\n\n\n

            Take a look at your data once again. What do you see? Event streams<\/a>? APIs<\/a> with optional fields? Data lakes<\/a> with inconsistent schemas? Pipelines for analytics<\/a> with the ability to merge multiple sources? <\/p>\n\n\n\n

            In all of those scenarios, the SQL COALESCE<\/code> function is of immense<\/em> value. It acts as a stabilizer in an unstable world of data. Update NULL<\/code> 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. <\/p>\n\n\n\n

            However, like everything in life, there are trade-offs to consider when using the SQL COALESCE<\/code> function. Take the following query, for example:<\/p>\n\n\n\n

            SELECT COALESCE(users, 1);<\/code><\/p>\n\n\n\n

            This query would return ‘1’ once a value in a column is NULL<\/code> – but what does that number signify? Is it NULL<\/code> (unknown value) or ‘1’ (exactly one row?) You\u2018re now no longer dealing with a substitution. <\/p>\n\n\n\n

            Bottom line: in modern data systems (such as event streams and APIs), the SQL COALESCE<\/code> 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<\/code> function deliberately (not just to fill gaps, but to preserve meaning) – and make sure it’s right for your use case before implementation!<\/strong><\/p>\n\n\n\n

            Alternatives to the SQL COALESCE function<\/h2>\n\n\n\n

            When it comes to your specific use case, the SQL COALESCE<\/code> 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.<\/strong> Some alternatives to the SQL COALESCE<\/code> function are:<\/strong><\/p>\n\n\n

            \n
              \n
            1. IFNULL<\/code> for MySQL<\/a> and SQLite<\/a> taking two arguments. <\/li>\n<\/ol>\n<\/div>\n\n
              \n
                \n
              1. ISNULL<\/code> for SQL Server<\/a> and Sybase<\/a> taking two arguments. <\/li>\n<\/ol>\n<\/div>\n\n
                \n
                  \n
                1. NVL<\/code> for Oracle<\/a> taking two arguments. <\/li>\n<\/ol>\n<\/div>\n\n\n
                  Alternative<\/strong> <\/td>About<\/strong> <\/td>Example<\/strong> <\/td><\/tr>
                  IFNULL<\/code> <\/td>Returns the first value if it is not NULL<\/code>. Otherwise, returns the second value. Only works with MySQL and SQLite. <\/td>SELECT IFNULL(user, 1) AS user_new FROM stats; <\/code><\/td><\/tr>
                  ISNULL<\/code> <\/td>Returns the first value if it is not NULL<\/code>, otherwise returns the second value. Only works with SQL Server and Sybase. <\/td>SELECT ISNULL(city, \u201cUnknown\u201c) AS city_new FROM users; <\/code><\/td><\/tr>
                  NVL<\/code> <\/td>Returns first value if it is not NULL<\/code>, otherwise returns the second one. Only works with Oracle. <\/td>SELECT NVL(salary, 0) AS salary_new 
                  FROM employees; <\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

                  Here are some of the key differences between the SQL COALESCE<\/code> function and those detailed above: <\/strong><\/p>\n\n\n\n

                  Function<\/strong> <\/td>Database<\/strong> <\/td>Notes<\/strong> <\/td><\/tr>
                  COALESCE<\/code> <\/td>All databases supporting standard SQL <\/td>Portable, standard-compliant, takes 2+ arguments <\/td><\/tr>
                  IFNULL<\/code> <\/td>MySQL, MariaDB, SQLite <\/td>Takes only 2 arguments, no more <\/td><\/tr>
                  ISNULL<\/code> <\/td>SQL Server <\/td>Takes only 2 arguments, no more <\/td><\/tr>
                  NVL<\/code> <\/td>Oracle <\/td>Takes only 2 arguments, no more <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

                  All three of these queries would produce the same output \u2013 cool, right? <\/p>\n\n\n\n

                  SELECT [IFNULL|ISNULL|NVL](email, \u201cnoemail@example.com\u201c) AS contact_email FROM users; <\/code><\/p>\n\n\n\n

                  \"an
                  IFNULL<\/code> in MySQL<\/em> <\/figcaption><\/figure>\n\n\n\n

                  Summary: why you should use the SQL COALESCE function<\/h2>\n\n\n\n

                  Contrary to popular belief, the SQL COALESCE<\/code> 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. <\/p>\n\n\n\n

                  The real strength of this function lies not only in filling gaps but also in making serious business decisions. While there are some<\/em> replacements for the SQL COALESCE<\/code> function, many DBAs rightfully choose it over its counterparts when business logic is at stake. <\/p>\n\n\n\n

                  \n
                  \n
                  \n
                  \n

                  Simple Talk is brought to you by Redgate Software<\/h3>\n
                  \n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n
                  \n

                  FAQs: SQL COALESCE Function<\/h2>\n\n

                  1. What does the COALESCE function do in SQL?<\/h3>\n
                  \n

                  The COALESCE<\/code> function returns the first non-NULL<\/code> value<\/strong> from a list of expressions, making it useful for handling missing or incomplete data.<\/p>\n <\/div>\n

                  2. When should I use COALESCE in SQL?<\/h3>\n
                  \n

                  Use COALESCE<\/code> when you need to replace NULL<\/code> values<\/strong>, set default values<\/strong>, or create fallback logic<\/strong> in queries, calculations, or sorting.<\/p>\n <\/div>\n

                  3. What is the syntax of COALESCE?<\/h3>\n
                  \n

                  The basic syntax is:

                  \nCOALESCE(value1, value2, ..., valueN)<\/code>

                  \nIt evaluates each value in order and returns the first one that is not<\/strong> NULL<\/code>.<\/p>\n <\/div>\n

                  4. Can COALESCE be used in SELECT and UPDATE statements?<\/h3>\n
                  \n

                  Yes. COALESCE<\/code> is commonly used in SELECT<\/strong><\/code> queries to display default values, and in UPDATE<\/strong><\/code> statements to replace NULL<\/code>(s) in a table.<\/p>\n <\/div>\n

                  5. What\u2019s the difference between COALESCE and ISNULL\/IFNULL\/NVL?<\/h3>\n
                  \n

                  COALESCE<\/code> is standard SQL<\/strong> and supports multiple arguments, while ISNULL<\/code>, IFNULL<\/code>, and NVL<\/code> are database-specific<\/strong> (and typically accept only two arguments.)<\/p>\n <\/div>\n

                  6. Does COALESCE affect query performance?<\/h3>\n
                  \n

                  In most cases, COALESCE<\/code> has minimal performance impact<\/strong>, but excessive use in large queries or indexes can affect optimization depending on the database.<\/p>\n <\/div>\n

                  7. When should you NOT use the SQL COALESCE function?<\/h3>\n
                  \n

                  Avoid using the SQL COALESCE<\/code> function when replacing NULL<\/code>(s) – it could change the meaning of your data<\/strong>, especially in analytics or reporting where NULL<\/code> represents unknown values.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"

                  Learn how the SQL COALESCE function works, with practical examples for handling NULL values, setting defaults, and writing cleaner, more reliable queries.…<\/p>\n","protected":false},"author":339547,"featured_media":104555,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,145792,143524,143531],"tags":[5854,4150,4151,4252],"coauthors":[146040],"class_list":["post-110277","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-mysql","category-sql-server","category-t-sql-programming-sql-server","tag-mysql","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110277","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/339547"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=110277"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110277\/revisions"}],"predecessor-version":[{"id":110296,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/110277\/revisions\/110296"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/104555"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=110277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=110277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=110277"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=110277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}