COALESCE

The Complete Guide to the SQL COALESCE Function

intro

Learn how to use COALESCE in SQL through some real-world examples in different RDBMS technologies.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

NULLs are tricky, and databases have found different ways to deal with them. This is such a common problem that the SQL standard involves functions to handle them consistently. In most cases, you need to replace NULLs with default values, which is what the SQL COALESCE function is all about!

In this article, you will discover everything you need to know about COALESCE, exploring its syntax, how it works, the DBMSs that support it, and its use cases.

Let's dive in!

What Is COALESCE in SQL?

COALESCE is an SQL function that accepts a list of parameters and returns the first non-NULL value from the list. Here is what the syntax looks like:

COALESCE(expression1, expression2, expression, ...) expression1, expression2, expression3, and so on, are the parameters (values or expressions) to be evaluated.

Usually, the SQL COALESCE function is used for NULL handling in the database. Instead of replacing NULL values at the application level, it allows you to deal with them directly at the data retrieval time.

How To Use the COALESCE SQL Function

To understand how to use COALESCE in SQL, you first need to learn how it works:

  1. The function evaluates the expressions received as input from left to right.
  2. It returns the first non-NULL expression it encounters.
  3. If all expressions are NULL, it returns NULL.

In other words, to use the COALESCE SQL function, you have to specify your columns, expressions, or values so that if an element is NULL, it will be replaced by the element to its right, and so on. To better understand how this mechanism works, consider this sample query:

Copy
        
1 SELECT COALESCE(bonus1, bonus2, bonus3) AS bonus 2 FROM sales_data;

As you can verify from the image below, it returns:

  • “Data not found” when bonus1 and bonus2  are NULL.
  • bonus1 when bonus1 is not NULL.
  • bonus2 when bonus1 is NULL and bonus2 is not NULL.
Launching the COALESCE query in DbVisualizer

Databases That Support COALESCE

COALESCE has been part of the ANSI SQL standard since 1992 and it is also an SQL reserved keyword. This means it is not a new feature and most RDBMS technologies provide a standard-compliant implementation.

The list of databases that support the SQL COALESCE function include:

  • MySQL
  • PostgreSQL
  • SQL Server (T-SQL)
  • Oracle Database
  • SQLite
  • IBM Db2
  • MariaDB
  • Amazon Redshift
  • Google BigQuery
  • Snowflake

All these DBMSs provide a COALESCE function that respects the syntax and logic presented earlier. The function is so widespread that finding modern database technologies that do not offer it is not easy. However, some proprietary or older database systems may not support it. This is generally not a problem as you can achieve the same result with custom logic or a CASE statement.

Check out our guide for an in-depth tutorial on PostgreSQL COALESCE.

SQL COALESCE Function: Use Cases

Now that you know how to use COALESCE in SQL, it is time to explore some popular use cases.

Note: The examples below are in MySQL, but the following scenarios apply to any DBMS that supports COALESCE. The query will be run in DbVisualizer, a fully-featured database client that supports dozens of databases.

Providing Default Values

The most common COALESCE SQL example involves using the function to provide default values for columns or values that might be NULL

For example, suppose you want to display the string "N/A" for missing data in a report. You can get this result directly in the database with the following query:

Copy
        
1 SELECT 2 name, 3 COALESCE(age, 'N/A') AS age, 4 department 5 FROM employee;
Note that NULL values became "N/A" strings

Handling NULL Values in Math Operations

Databases can produce unexpected behavior when adding, dividing, or multiplying by NULL values. To avoid that, you can use the SQL COALESCE function to replace the NULLs with more consistent values.

Consider for example the query below:

Copy
        
1 SELECT 2 name, 3 price, 4 discount, 5 price * (1 - COALESCE(discount, 0)/100) AS final_price 6 FROM product;

As you can notice in the following image, COALESCE makes sure that it produces consistent results even when discount is NULL.

Note how "final_price" is correct even when "discount" is NULL

Similarly, you can use this mechanism when using SQL aggregate functions:

Copy
        
1 SELECT 2 category, 3 SUM(COALESCE(price, 0)) as global_price 4 FROM product 5 GROUP BY category;
Note the use of COALESCE in the SUM aggregate funcion

Sorting and Ordering

COALESCE can be used to control the sorting order of result sets, ensuring that NULL values are treated as expected.

Copy
        
1 SELECT name, COALESCE(priority, 0) AS priority 2 FROM tasks 3 ORDER BY priority;
NULLs became 100s and led to a consistent ordering

DBMS technologies behave differently when it comes to ordering values that may be NULL, but this approach leads to consistent and predictable results.

Without COALESCE, you would get a different sorting:

The ordering changed without COALESCE

Note that “Task 2” and “Task 4” moved from the bottom to the top of the resulting table.

Et voilà! You just became a COALESCE SQL master!

Conclusion

In this guide, you learned what the SQL COALESCE function is and how it works. You now know that it is a powerful, standards-compliant, and easy-to-use function for handling NULL values directly in the database.

To better appreciate its capabilities, you need a tool that allows you to visually explore query results. This is where a comprehensive database client like DbVisualizer comes in! In addition to supporting several DBMSs, this tool offers advanced query optimization capabilities, ERD-like schema generation, and much more. Try DbVisualizer for free today!

FAQ

What does COALESCE do in SQL?

The COALESCE SQL function accepts a comma-separated list of arguments and returns the first argument that is not NULL.

How is COALESCE implemented in SQL?

COALESCE is implemented just like in the syntax below:

COALESCE(expression1, expression2, expression3, ...)

As such, COALESCE is nothing more than a shorthand for the following SQL CASE expression:

Copy
        
1 CASE WHEN expression1 IS NOT NULL THEN expression1 2 WHEN expression2 IS NOT NULL THEN expression2 3 WHEN expression3 IS NOT NULL THEN expression3 4 ... 5 END

COALESCE is a standard SQL function supported by most databases. Its purpose is to handle multiple values and provide the first non-NULL result. In contrast, ISNULL is not a standard function and behaves differently in different databases. For example, on SQL Server, it returns a specified value if the expression is NULL. So, the COALESCE vs ISNULL comparison depends on the DBMS.

The COALESCE SQL function was introduced to the SQL ANSI standard in SQL-92 (also known as SQL2). SQL-92 is a widely adopted SQL standard that was published in 1992. This is why it is supported by most relational database management systems and has become a common feature in SQL databases.

Microsoft Access does not support the COALESCE function. Instead, you can use the native IIf or Nz functions to handle NULL values and achieve similar functionality.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Outer Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 11 min 2024-02-19
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL Views: A Comprehensive Guide

author Bonnie tags VIEWS 5 min 2024-02-12
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

Dangerous Big Data - Big Data Pitfalls to Avoid

author Lukas Vileikis tags BIG DATA min 2024-02-01

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗