COALESCE() Function in SQL

COALESCE() Function Syntax

The COALESCE function in sql accepts the n number of values as parameters and returns the first non-null value.

Suppose a student table contains details such as id, name, father_name, mother_name, and guardian_name. Now, we need to write a query to fetch the contact person's name for every student. The contact person can be either father, mother, or guardian.

We can use the COALESCE function in sql with father_name, mother_name, and guardian_name. It returns the first non-null value of the three values. If all values are NULL, it returns NULL.

• For the student with id 1, the coalesce method returns Howard Stark because it is the first non-null value of the columns father_name, mother_name, and guardian_name.
• For the student with id 2, the coalesce method returns May Parker because it is the first non-null value of the columns father_name, mother_name, and guardian_name.
• For the student with id 2, the coalesce method returns NULL as all the column values are NULL.

Parameter Values

• val_1, val_2, ....., val_n - The Coalesce function in sql takes a list of values that need to be tested.

COALESCE() Function Examples

Use the student table below to understand COALESCE with different examples. The empty cells refer to the NULL value.

The SQL queries to create the above table and insert records are

Example 1: Coalesce() with Single Parameter

If we only consider the father_name for the contact name, then that student whose father name is not given will return NULL as output.

Output:

Explanation

For the students with id 3 and 2, it returned Null as all the values are NULL.

Example 2: Coalesce() with Multiple parameters

Let's run the COALESCE query on the above table to find the contact person's name, father_name, mother_name, or guardian_name. If all the values are NULL, return NULL.

Output:

Explanation

• For the student with id 1, it returned the father_name as contact_person as it is the first non-null value.
• For the student with id 2, it returned guardian_name as contact_person
• For the student with id 3, it returned NULL as all the values are NULL.

Example 3: Coalesce() with explicit return value

Let's run the COALESCE query on the above table to find the contact person's name, father_name, mother_name, or guardian_name. If all the values are NULL, return N/A.

Output:

Explanation

For the student with id 3, it returned N/A as all the values are NULL.