Skip to main content
HomeTutorialsSQL

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Oct 2022  · 4 min read

What is the COALESCE() function?

Coalesce returns the first non-null value in a list. If all the values in the list are NULL, then the function returns null. 

When to 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

[email protected]

null

[email protected]

2

null

[email protected]

[email protected]

3

[email protected]

[email protected]

[email protected]

COALESCE() syntax

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

The COALESCE() function takes in at least one value (value_1). It will return the first value in the list that is non-null. 

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

Example 1: COALESCE() a column with a constant

Consider the table countries with a list of countries and their national days. Some national days are empty. For rows with empty national days, we can fill it in with the value 'Unknown'

The query and the result are as follows. 

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

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: COALESCE() 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 query and the result are as follows.

SELECT DISTINCT
   product_name,
   description,
   COALESCE(
      CASE WHEN 
         LENGTH(description) >= 60 
         THEN NULL 
         ELSE description 
         END, 
      product_name) product_name_or_description
FROM products
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: COALESCE() three columns or more

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 so, 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

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.

Technical requirements

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

See also

Learn more about SQL

Topics

Popular SQL Courses

Certification available

Course

Introduction to SQL

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

10 Top Data Analytics Conferences for 2024

Discover the most popular analytics conferences and events scheduled for 2024.
Javier Canales Luna's photo

Javier Canales Luna

7 min

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

Mastering SQL ROUND: Precision Handling in Data Analysis

Learn how to use the SQL ROUND function in this tutorial. It covers the implementation of the ROUND function in Oracle, MySQL, PostgreSQL, and SQL Server.
Laiba Siddiqui's photo

Laiba Siddiqui

10 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More