---
title: "Explain the purpose of the COALESCE() function in SQL and provide an example."  
description: "Explain the purpose of the COALESCE() function in SQL and provide an example."  
author: "Revati S Misra"  
published: 2023-06-30  
updated: 2023-07-01  
canonical: https://www.mindstick.com/forum/158915/explain-the-purpose-of-the-coalesce-function-in-sql-and-provide-an-example  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# Explain the purpose of the COALESCE() function in SQL and provide an example.

[Explain the purpose](https://www.mindstick.com/forum/159480/explain-the-purpose-of-the-virtual-keyword-and-virtual-functions-in-c-plus-plus) of the [COALESCE](https://www.mindstick.com/interview/33815/what-is-the-use-of-coalesce-in-sql-server)() [function in SQL](https://www.mindstick.com/forum/156839/what-is-function-in-sql-server) and provide an example.

## Replies

### Reply by Aryan Kumar

The COALESCE() [function](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server) in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) is used to return the first non-NULL value from a list of expressions. The syntax for the COALESCE() function is:

SQL

```plaintext
COALESCE(expression1, expression2, expression3, ...);
```

- `expression1`, `expression2`, `expression3`, etc. are the expressions that you want to evaluate.

If any of the expressions evaluate to a non-NULL value, then that value will be returned. If all of the expressions evaluate to NULL, then NULL will be returned.

For example, the following query will return the first non-NULL value from the `first_name`, `last_name`, and `email` columns of the `users` table:

SQL

```plaintext
SELECT COALESCE(first_name, last_name, email)
FROM users;
```

If the `first_name` column is not NULL, then the value of the `first_name` column will be returned. If the `first_name` column is NULL, then the value of the `last_name` column will be returned. If both the `first_name` and `last_name` columns are NULL, then the value of the `email` column will be returned.

The COALESCE() function can be used to handle NULL values in a variety of ways. For example, you can use it to:

- Return a default value if a column is NULL.
- Fill in missing values in a table.
- Filter results based on the presence or absence of NULL values.

The COALESCE() function is a versatile tool that can be used to simplify your queries and improve the performance of your database.

Here are some other examples of how the COALESCE() function can be used:

- To return the first non-NULL phone number from a list of phone numbers:

SQL

```plaintext
COALESCE(phone_number1, phone_number2, phone_number3);
```

- To return the first non-NULL date of birth from a list of dates of birth:

SQL

```plaintext
COALESCE(date_of_birth1, date_of_birth2, date_of_birth3);
```

- To return the first non-NULL value from a list of expressions, regardless of the data type of the expressions:

SQL

```plaintext
COALESCE(first_name, last_name, email, birthdate, phone_number);
```


---

Original Source: https://www.mindstick.com/forum/158915/explain-the-purpose-of-the-coalesce-function-in-sql-and-provide-an-example

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.