Coalesce Function

This is one of the useful functions in SQL where we substitute the value with something else if it is NULL. 

 

Example:

• Suppose we are fetching age from the database for all the Employees and for some employees if their age is NULL, then we want to substitute it with some value (let’s say 20). 
• Suppose we want Name column from a table such that If First Name is NULL, then use Last Name, if Last Name is NULL then use “Not Available”.

 

For such scenarios, the Coalesce function is very useful.

Other than the COALESCE function, we have IFNULL as well as CASE Statement which serve almost the similar purpose.

 

Let us see the query and use case:

 

Query:

SELECT 

       First_Name, 

       Last_Name, 

       Age, 

       COALESCE(First_Name, Last_Name, 'Not_Available') as Full_Name_Coalesce,

       IFNULL(First_Name, 'Not_Available') as Full_Name_If_Null

FROM Student;

 

Output:

 

COALESCE Result:

As we can see, for the 2nd row, the first name is null, hence COALESCE used the last name. 

In the 5th row, the first and last name both are NULL, hence the Full Name that it returned is “Not_Available”. If all the arguments of function are NULL, the COALESCE function returns NULL.

 

IFNULL Result:

For 2nd, 3rd and 5th row, the First name is NULL, hence Full Name that it returned is “Not_Available”

 

 

What is the difference between COALESCE and IFNull?

COALESCE is used when you have 2 or more than 2 arguments in the function. Whereas, IFNull function can only take 2 arguments.