Back to Top

MySQL coalesce() function

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 the MySQL functions.Let’s understand the interesting function of MySQL.

If you don’t want to learn about some more functionalities of MySQL, read Table Optimization in MySQL and MySQL SHOW Commands.

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

Let’s see Coalesce function by example

First of all, let’s create table

Now let’s create query with using Coalesce function

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.

And if all the values are NULL in the list, It will return the 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.

This is very useful especially when you want to find out the not null values from multiple strings.Do you any other better way? Do let me know via the comment section.

Share:
Share on Facebook0

Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Comments (3)

  1. I think it would be great that if after the last value is checked and all are null, you could also specify a default value. Is this possible?

  2. Yes, you can set the default value by adding it as the last parameter like this:

    SELECT COALESCE(field1, field2, field3, ‘default’);

    In this case if all the fields 1-3 are NULL, ‘default’ is returned.

  3. I am truly delighted to read this web site posts which includes lots of helpful data,
    thanks for providing such statistics.

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

How to Define and Call Case functions in PHP

Posted on 6 months ago

Bhumi

How to Accordion Menu with Modx

Posted on 4 years ago

Bhumi

How To set up Signup Bonus in X-Cart

Posted on 4 years ago

Bhumi

How does CI/CD works on AWS?

Updated 17 years ago

Bhumi

Overview of Amazon Web Services (AWS)

Updated 17 years ago

Bhumi

How to add ColorDots in FullCalender?

Updated 17 years ago

Bhumi

A Beginner’s Guide To Composer

Updated 17 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 1 year ago

Bhumi

The Reader’s Poll – August 2014

Posted on 2 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 2 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 2 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 3 years ago

Bhumi