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.
[cc lang=”mysql”]
SELECT COALESCE(NULL, NULL, 1);
— Return 1
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1);
— Return 1
[/cc]
This function will return NULL if you pass all NULL arguments. Let’s look at the example for the same:
[cc lang=”mysql”]
SELECT COALESCE(NULL, NULL, NULL, NULL);
— Return NULL
[/cc]
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:
[cc lang=”mysql”]
SELECT COALESCE(field_name,0) as value from table;
[/cc]
This function will return NULL if all arguments are NULL.
Hope you have enjoyed this quicktip for MySQL and subscribe to our RSS Feed.
