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

Question

Aryan Kumar · Answer

The COALESCE() function in SQL is used to return the first non-NULL value from a list of expressions. The syntax for the COALESCE() function is:

SQL

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

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

COALESCE(phone_number1, phone_number2, phone_number3);

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

SQL

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

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

forum

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

Can you answer this question?

1 Answers