How to Use the COALESCE() Function in SQL (With Examples)

Syntax of COALESCE()

COALESCE(value_1, value_2, ...., value_n)

The COALESCE() function takes in at least one value (value_1). It will return the first non-null value in the list, from left to right.

For example, it will first check if value_1 is null. If not, then it returns value_1. Otherwise, it checks if value_2 is null. The process goes on until the list is complete. 

COALESCE() can be used with columns, expressions, or constants.

Practical Examples of COALESCE()

Example 1: Replacing null with a constant

Consider the table countries with a list of countries and their national days. Some national day values are null. COALESCE() fills missing values in national_day with the constant string 'Unknown'.

SELECT
   country_id,
   name, 
   national_day,
   COALESCE(national_day, 'Unknown') AS national_day_coalesced
FROM countries
ORDER BY country_id

The results are as follows:

Notice how the null value in national_day is replaced by a constant Unknown. 

Example 2: Choosing between two columns

We have a table named products. It contains the product name and its description. Some descriptions are too long (more than 60 characters). In that case, we replace the description with the product name.

The following query uses CASE to convert long descriptions to NULL, then uses COALESCE() to fallback to the product name.

SELECT DISTINCT
   product_name,
   description,
   COALESCE(
      CASE WHEN 
         LENGTH(description) >= 60 
         THEN NULL 
         ELSE description 
         END, 
      product_name) product_name_or_description
FROM products

The results are as follows:

Notice how the column product_name_or_description displays the product_name if the description is long. Otherwise, it displays the description.

Example 3: Fallback logic with multiple columns

We can take example 2 one step further. Assume that there are currently two requirements:

• If the length of the description is less than 60, then display the description.
• Otherwise, check if the length of the product_name is less than 20. If it is, we display the product_name.
• Otherwise, display product.

SELECT DISTINCT
   product_name,
   description,
   COALESCE(
      CASE 
         WHEN LENGTH(description) > 50
         THEN NULL
         ELSE description
      END,
   CASE 
      WHEN LENGTH(product_name) > 14
      THEN NULL
      ELSE product_name
   END,
'product') AS product_name_or_description
FROM products
ORDER BY product_name

The results are as follows:

Notice how the column product_name_or_description displays either the product_name or the description depending on the lengths of the product_name or description.

Supported SQL Engines

COALESCE() works in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse, BigQuery, and Amazon RedShift.

Related SQL Functions

• FORMAT() SQL Function
• DATEDIFF() SQL Function
• INSERT INTO SQL Function
• COUNT() SQL Function

Final Thoughts

The COALESCE() function is a versatile tool for handling null values and simplifying your SQL queries. Whether you're replacing missing data with defaults or combining multiple columns into one, COALESCE() helps keep your logic clean and readable.

Ready to deepen your SQL skills? Check out these beginner-friendly and career-boosting courses on DataCamp:

• Introduction to SQL

• Intermediate SQL

• SQL Fundamentals Career Track