How to use MySQL COALESCE function

MySQL is a DBMS that has a concept of Null values. In MySQL, COALESCE function is used as it returns the first non-null value from the specified series of expressions. If all values of the column are null then COALESCE will also return the null. It works similarly to “IS NULL query ”; both deals with the NULL values but the difference in both of them is of their behavior, “IS NULL” query extracts the null values from the table whereas the COALESCE function gives the first non-null value if there is any null value present in the table.

In this write-up, we will explain what COALESCE function is in MySQL and how it can be used with the help of examples.

What is the COALESCE function in MySQL

The COALESCE function helps to identify the first non-null value from the given data, and if there is any null value in the selected table, it will return “null”. Before going ahead to understand the examples let’s discuss the general syntax of the COALESCE.

General syntax is:

SELECT COALESCE (value1,value2,.....);

To understand this syntax of COALESCE function, we will take some examples of the integers.

SELECT COALESCE (1,2,3);

As there is no null value so it gives the first value among the compared values. If there is the first null value in the comparison values then the COALESCE function will give the first value which is after the null value.

SELECT COALESCE (null,2,3);

If the null is present in the second position, then the COALESCE should return the non-zero value present in the first position.

SELECT COALESCE (12,null,3);

If the null value is present in the third position then the COALESCE function will return back the first non-null value as a result.

SELECT COALESCE (121,34,null);

Now, let’s take a case in which all the values of the table are null, run the following command

SELECT COALESCE (null,null,null);

From the output, it has been cleared that if all values are null then the output will be null. If we summarize the results of the above examples then we can say that if there is a list of numbers and we are supposed to take the values out using the reference of “null value”, then if a null value is present on the first position, the COALESCE function will return the first non-null value after the null value, if a null value is present on any other position other than first, then the COALESCE function will find the first non-null value which is present in the list of of the numbers being compared using the COALESCE function and if all the values are null then the COALESCE function will give the null value in the output.

Consider a table with the name of “Student_names” in the database whose data can be displayed.

SELECT * FROM Student_names;

Now we want to compare the values of First_name, Middle_name, and Last_name using the COALESCE function with reference to a null value and return the result as Name.

SELECT student_id, COALESCE (first_name, middle_name,last_name) as Name FROM Student_names;

In the output, we can see that in student_id=2, the null entry is in the Middle_name so it took the name of First_name, in student_id=3, it chose the Middle_name because the null value is at First_name, in student_id=4, it choose the First_name as the null value is in Last_name and in the student_id=5, all the values are null so it returns the null. If all the values are null then it can print what you write in the command, for example, we want to print that if all values are null then print “No_name_found”.

SELECT student_id, COALESCE (first_name, middle_name,last_name,”no_name_found”) as Name FROM Student_names;

We can see from the output when all the values are null it prints the text we write in the command instead of returning the null value.

Conclusion

COALESCE and IS NULL query are both the same as they deal with the null values. Using the COALESCE we can compare more than one column where the “IS NULL” query is applicable only on one column. In this write-up, we have explained with the simple examples of the COALESCE to understand its working and then we take an example of the table to understand how the COALESCE function works on the table.

How to use MySQL COALESCE function

What is the COALESCE function in MySQL

Conclusion

About the author

Hammad Zahid