MYSQL COALESCE() FUNCTION

If you are working with MySQL, You might have the idea about that MySQL provides the various function which you can use when you require but before that it is necessary to understand theMySQL functions.Let’s understand the interesting function of MySQL.

What is Coalesce?

Coalesce is a MySQL function and it takes first non-null value in the list.If all value evaluates to null, then the coalesce function will return null. coalesce check all fields until non-null value found.COALESCE is a standard MySQL function which can take one or more arguments.COALESCE function can be used when you can use some other value if a NULL value in the field.

Syntax:

Here is the syntax for the Coalesce function in MySQL

1 2 3

COALESCE (field, field [, field] ...)

Let’s see Coalesce function by example

First of all, let’s create table

1 2 3 4 5 6 7 8 9

CREATE table person (     id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,     FirstName VARCHAR( 255 ) NULL DEFAULT NULL ,     MiddleName VARCHAR( 255 ) NULL DEFAULT NULL,     LastName VARCHAR( 255 ) NULL DEFAULT NULL )

Now let’s create query with using Coalesce function

1 2 3

SELECT COALESCE( FirstName,MiddleName,LastName ) AS name FROM person

Explanation:

The above example, I have used three arguments to Coalesce function and above query returns name from the FirstName, MiddleName, LastName and its returns value of MiddleName if FirstName doesn’t have any value or NULL. then same for LastName, it returns LastName value if FirstName and MiddleName don’t have any value or NULL. and it returns NULL if all three FirstName, MiddleName and LastName are null.

As Coalesce function returns the first non-NULL value from the list, so below code will return the 1 as output instead of NULL.

1 2 3

SELECT COALESCE(NULL,1);//OUTPUT: 1

And if all the values are NULL in the list, It will return the NULL

1 2 3

SELECT COALESCE(NULL,NULL,NULL);//OUTPUT: NULL

Open your MySQL Query Editor and copy paste the above code, Check how to it works. It is very simple,isn’t it?

NOTE:COALESCE MySQL function works for null value but it can’t work an empty field.so need to set field as NULL if empty.