Skip to main content

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

Learn how to use the SQL COALESCE() function to handle null values, combine columns, and clean up your data with real-world examples and tips.
Updated Mar 27, 2025  · 5 min read

When working with real-world data, missing values are almost inevitable. Whether you’re cleaning datasets or merging columns, SQL offers a simple yet powerful solution: the COALESCE() function. This tutorial will show you how COALESCE() works, when to use it, and how to apply it through practical examples—all in just a few lines of SQL.

What Is COALESCE() in SQL?

The COALESCE() function in SQL returns the first non-null value from a list of expressions. If all values are null, it returns null. It’s commonly used to handle missing values or combine multiple columns into one fallback output.

When Should You Use COALESCE()?

This function is useful when combining the values from several columns into one.

For example, a table called users contains values of users' work_email and personal_email.

Using the COALESCE() function, we can create a column called email, which shows the user's work_email if it is not null. Otherwise, it shows personal_email.

user_id

work_email

personal_email

COALESCE() of

work_email and

personal_email

1

angel@datacamp.com

null

angel@datacamp.com

2

null

bruce@gmail.com

bruce@gmail.com

3

cath@datacamp.com

cath@gmail.com

cath@datacamp.com

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()

Run and edit the code from this tutorial online

Run code

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:

country_id

name

national_day

national_day_coalesced

1

Aruba

null

Unknown

2

Afghanistan

1919-08-19T00:00:00.000Z

1919-08-19

3

Angola

1975-11-11T00:00:00.000Z

1975-11-11

4

Anguilla

1967-05-30T00:00:00.000Z

1967-05-30

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:

product_name

description

product_name_or_description

G.Skill Ripjaws V Series

"Speed:DDR4-3200,Type:288-pin DIMM,CAS:14Module:4x16GBSize:64GB"

G.Skill Ripjaws V Series

G.Skill Ripjaws V Series

"Speed:DDR4-3200,Type:288-pin DIMM,CAS:15Module:4x16GBSize:64GB"

G.Skill Ripjaws V Series

Asus X99-E-10G WS

"CPU:LGA2011-3,Form Factor:SSI CEB,RAM Slots:8,Max RAM:128GB"

"CPU:LGA2011-3,Form Factor:SSI CEB,RAM Slots:8,Max RAM:128GB"

Supermicro X9SRH-7TF

"CPU:LGA2011,Form Factor:ATX,RAM Slots:8,Max RAM:64GB"

"CPU:LGA2011,Form Factor:ATX,RAM Slots:8,Max RAM:64GB"

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:

product_name

description

product_name_or_description

ADATA ASU800SS-128GT-C

Series:Ultimate SU800,Type:SSD,Capacity:128GB,Cache:N/A

product

ADATA ASU800SS-512GT-C

Series:Ultimate SU800,Type:SSD,Capacity:512GB,Cache:N/A

product

AMD 100-5056062

Chipset:Vega Frontier Edition Liquid,Memory:16GBCore Clock:1.5GHz

product

AMD 100-505989

Chipset:FirePro W9100,Memory:32GBCore Clock:930MHz

Chipset:FirePro W9100,Memory:32GBCore Clock:930MHz

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.

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:

Associate Data Engineer in SQL

Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.

FAQs

What happens if all values in COALESCE() are NULL?

If every argument passed to the COALESCE() function is NULL, the function will return NULL.

How is COALESCE() different from ISNULL() or IFNULL()?

  • ISNULL() (SQL Server) and IFNULL() (MySQL, SQLite) only accept two arguments.
  • COALESCE() can accept multiple arguments and is more standard across SQL dialects.

  • COALESCE() is part of the ANSI SQL standard, while ISNULL() and IFNULL() are database-specific.

Can I use COALESCE() with expressions or functions?

Yes, you can use column names, literals, functions, or expressions inside COALESCE().

COALESCE(LOWER(name), 'unknown')

Is there a performance cost to using COALESCE()?

In general, no—COALESCE() is efficient. However, if you use it with complex expressions or inside large queries, the database may evaluate more expressions than needed, depending on how it's written.

Does COALESCE() work with different data types?

Yes, but all arguments should be implicitly convertible to a common data type. Otherwise, it may return a type conversion error depending on your SQL engine.

Can I nest COALESCE() functions?

Yes. You can nest them, though it’s rarely necessary since COALESCE() already handles multiple arguments:

COALESCE(col1, COALESCE(col2, 'default'))

This is equivalent to:

COALESCE(col1, col2, 'default')
Topics

Popular SQL Courses

Course

Introduction to SQL

2 hr
1.1M
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

Tutorial

How to Use the SQL EXISTS() Operator

Learn how to use the SQL EXISTS() operator for subquery evaluation and filtering, complete with examples, best practices, and tips for optimizing your queries.
Allan Ouko's photo

Allan Ouko

10 min

Tutorial

Aggregate Functions in SQL

Learn how to use aggregate functions for summarizing results and gaining useful insights about data in SQL.
Sayak Paul's photo

Sayak Paul

9 min

Tutorial

How to Use the SQL REPLACE() Function

Learn the application of the SQL REPLACE() function in text manipulation. Understand the use of the REPLACE() function in data cleaning and database management.
Allan Ouko's photo

Allan Ouko

10 min

Tutorial

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

3 min

Tutorial

SQL String Functions: A Beginner's Guide

Understand how to use SQL String Functions to clean and process text data efficiently.
Eugenia Anello's photo

Eugenia Anello

7 min

Tutorial

Understanding the SQL DECODE() Function

Learn how to use DECODE() in Oracle for conditional logic in data transformation. Compare DECODE() to the CASE WHEN statement in performance and behavior.
Allan Ouko's photo

Allan Ouko

11 min

See MoreSee More