coalesce definition in Cambridge Dictionary:

  1. If two or more things coalesce, they come or grow together to form one thing or system.
  2. to combine into a single group or thing

But in MySQL, coalesce means:

COALESCE(): Return the first non-NULL argument

As an ESL(English as Second Language), That confused me a lot. From the meaning in the dictionary, coalesce is a synonym to combine or merge.

What I expect when I read coalesce, is:

COALESCE(): Concatenate all arguments

but it isn't.

Could anyone please explain that for me?

  • 5
    Keep in mind that just because a formal definition for a word is one thing, doesn't mean that a technical definition of the word is exactly the same (in this case at least there is some overlap, which isn't always the case). Instead, use the definition found in-context, in this case from the MySQL docs. – jleach Mar 7 '19 at 11:09
  • 1
    They had to find a name for a function returning the first non-null argument. "grow/melt together" is not very apt, but nicely nerdy. FIRSTPRESENT/FIRSTNONNULL/NONNULL? – Joop Eggen Mar 7 '19 at 13:04
  • 1
    The only person who can answer this question is the person who wrote that particular function. You need to ask them, not us. – Jörg W Mittag Mar 7 '19 at 16:06
  • IMO, it's no less strange to ask people who didn't invent COALESCE() why it's called that than it would be for someone to ask about the etymology of a "regular" English word outside of programming context. I'm happy to directly answer (or help research) why this-or-that plain English word is spelled the way it is without making people go back in time to ask Noah Webster.... – Jesse Amano Mar 18 '19 at 22:35
  • I read the reason why this word was chosen just yesterday. Unfortunately, I can't find it again, but the upshot was they looked in the dictionary to find a word that was reminiscent of the concept they wanted to convey, and COALESCE was chosen because it was the only word they could find that was not already in use by other software systems. – Robert Harvey May 2 '19 at 17:11

A good explanation for why it was named that comes from looking at motivating examples of what UNPIVOT does, and implementing it in terms of older SQL expressions.

Consider that you have an intermediate resultset with multiple possible sources for a given value, e.g. from a bunch of LEFT JOINs. You wish to "combine or merge" all these mostly null sources into one that holds the answer

I.e. you have something that looks like

One   1    Null Null Null Null 
Two   Null 2    Null Null Null 
Three Null Null 3    Null Null 
Four  Null Null Null 4    Null 
Five  Null Null Null Null 5

and want something that looks like

One   1 
Two   2 
Three 3 
Four  4 
Five  5   
| improve this answer | |

COALESCE isn't supposed to be used on a data series. It's supposed to receive several explicit expressions as arguments, one of which you hope will be non-NULL. Think of it as SQL's version of nested X ? Y : B operators, used to express several levels of fallback values when the normal value is NULL.

| improve this answer | |
  • But your answer doesn't explain why X ? Y : B named as COALESCE. Could you please give some information about that? – Sayakiss Mar 7 '19 at 12:13

The COALESCE() function is useful when you require the first non-null value from a list of values.

It takes multiple values as an argument. From the given arguments it will return the first non-null value.


The result of the above query will be 'Stack' as it is the first value that is not null.

| improve this answer | |
  • 2
    This explains what COALESCE does, but not why it's called that. The OP already knows what it does. – Sebastian Redl May 2 '19 at 6:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.