How to use Coalesce in MySQL
Solution 1
-
How come i saw queries that returns multiple values? isnt it only the first not null value that is returned?
Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.
-
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?
In order of its arguments: in this example,
column1
beforecolumn2
. -
Or if im wrong or my syntax is wrong, how do i properly write it?
You're not wrong.
-
Can someone provide a very good and simple example on how to use it?
Taken from the documentation:
mysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL
-
And when it is desirable to use.
It is desirable to use whenever one wishes to select the first non-NULL value from a list.
Solution 2
I personally use coalesce when I want to find the first column that isn't blank in a row from a priority list.
Say for example I want to get a phone number from a customer table and they have 3 columns for phone numbers named mobile, home and work, but I only want to retrieve the first number that isn't blank.
In this instance, I have the priority of mobile, then home and then work.
TABLE STRUCTURE
--------------------------------------------
| id | customername | mobile | home | work |
--------------------------------------------
| 1 | Joe | 123 | 456 | 789 |
--------------------------------------------
| 2 | Jane | | 654 | 987 |
--------------------------------------------
| 3 | John | | | 321 |
--------------------------------------------
SELECT id, customername, COALESCE(mobile, home, work) AS phone FROM customers
RESULT
------------------------------
| id | customername | phone |
------------------------------
| 1 | Joe | 123 |
------------------------------
| 2 | Jane | 654 |
------------------------------
| 3 | John | 321 |
------------------------------
Solution 3
COALESCE will return the first non-null column or value.
Example Usage:
SELECT COALESCE(my_column, my_other_column, 'default') as username FROM my_table;
Example results:
my_column my_other_column results
null null 'default'
null 0 '0'
null 'jimmy' 'jimmy'
'bob' 'jimmy' 'bob'
Belmark Caday
Updated on October 05, 2020Comments
-
Belmark Caday over 3 years
A little help here. I really don't understand how to use this coalesce in MySQL
I have read all the pages in page 1 result of
how to use coalsece
in google result.I know its meaning that it returns the first non-null value it encounters and null otherwise.
But it's still vague for me.
- How come I saw queries that returns multiple values? Isn't it only the first not null value that is returned?
- And how do it decide which column to base?
coalesce(column1,column2)
? what if first column is null and other column is not null? - Or if I'm wrong or my syntax is wrong, how do i properly write it?
- Can someone provide a very good and simple example on how to use it?
- And when it is desirable to use.
-
fenway about 11 yearsFurther to answer (5) -- also quite helpful when using LEFT JOINs.
-
eggyal about 11 years@fenway: Depends on the logic that one is trying to implement. Most
LEFT JOIN
s that I write don't useCOALESCE()
. -
Belmark Caday about 11 yearsoh right, i forgot, another question. When would I want to only query the first not null in thousand of records?
-
eggyal about 11 years@user2484763: SQL expressions, including functions like
COALESCE()
, are evaluated for each record; thus if records 1 and 2 have(column1, column2)
=(NULL, 5)
and(10, NULL)
respectively,COALESCE(column1, column2)
will return5
and10
when evaluating each record. -
EmmyS almost 6 yearsThis is a really helpful explanation. I've read a lot about how to use it, but couldn't really parse why I'd use it.
-
Payel Senapati almost 3 yearsBest practical example