How to use Coalesce in MySQL

Question

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.

`COALESCE()` will only return a single value -- the first non-null value, as you mentioned. If you have a specific example that defies this, please update your question. — fenway, Jun 14 '13 at 09:07

score 43 · Accepted Answer · answered Jun 14 '13 at 09:07

43

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.

answered Jun 14 '13 at 09:07

eggyal

122,705
18
212
237

4

Further to answer (5) -- also quite helpful when using LEFT JOINs. – fenway Jun 14 '13 at 09:08
@fenway: Depends on the logic that one is trying to implement. Most `LEFT JOIN`s that I write don't use `COALESCE()`. – eggyal Jun 14 '13 at 09:09
oh right, i forgot, another question. When would I want to only query the first not null in thousand of records? – Belmark Caday Jun 14 '13 at 09:12
1

@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 return `5` and `10` when evaluating each record. – eggyal Jun 14 '13 at 09:15

score 39 · Answer 2 · answered Feb 03 '17 at 11:19

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    |
------------------------------

This 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. — EmmyS, Sep 26 '18 at 23:14

Andrew · Answer 3 · 2017-08-07T20:14:37.120

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'

edited Aug 07 '17 at 20:14

answered Mar 03 '17 at 15:45

Andrew

18,680
13
103
118

score 0 · Answer 4 · edited Oct 04 '19 at 12:27

This is a perfect example of the Coalesce if will change the null fields/columns to what you want them to be in the case from null to 0 and 1.

Copy this into a mysql editor it will look a lot better

Select "Column1 (Dont Want Touched)", 
  coalesce(column2(That you want set to 0 if null), 0) as column2 /* (give it same name as was e.g. "column2") */, 
  coalesce(column3(Instead of null set to 1) ,1) as column3 /* (give it same name as was e.g. "column3") */ 
from "MydataTable" 
Where 'somedates' in ('2015-04-10', '2015-04-03', '2015-03-27', '2015-04-17')  and id = 10 order by 'somedates';

How to use Coalesce in MySQL

4 Answers4

Linked