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.
coalesce(column1,column2)
? what if first column is null and other column is not null?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
before column2
.
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.