COALESCE in MySQL

  • Avinash
  • 0
  • Jun 01, 2012
  • Database

Here I come with the quick but very usefult tip for the MySQL queries. I will explain COALESCE function of the MySQL.

COALESCE is used to get the first NOT NULL value from the passed arguments. This function can takes any number of arguments.

Let’s see how you can use this function.

SELECT COALESCE(NULL, NULL, 1); -- Return 1 SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1); -- Return 1
SELECT COALESCE(NULL, NULL, 1);
-- Return 1

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
-- Return 1

This function will return NULL if you pass all NULL arguments. Let’s look at the example for the same:

SELECT COALESCE(NULL, NULL, NULL, NULL); -- Return NULL
SELECT COALESCE(NULL, NULL, NULL, NULL);
-- Return NULL

This will be useful when your database field’s default value is set to NULL but you don’t want to get NULL as a return value but you want it to return 0. Then you can write your query as below:

SELECT COALESCE(field_name,0) as value from table;
SELECT COALESCE(field_name,0) as value from table;

This function will return NULL if all arguments are NULL.

Hope you have enjoyed this quicktip for MySQL and subscribe to our RSS Feed.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: