0

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?

closed as primarily opinion-based by Greg Burghardt, Robert Harvey May 2 at 17:08

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 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 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 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 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 at 17:11
4

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   
3

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.

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

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.

SELECT COALESCE(NULL,'Stack', NULL, 'Exchange', NULL);

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

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

The MySQL COALESCE function returns the first non-null expression in the list.

The syntax for the COALESCE function in MySQL is:

COALESCE( expression1, expression2, ... expression_n )

expression1 to expression_n The expressions to test for non-null values.

Note : If all expressions evaluate to null, then the COALESCE function will return null.

for example:

mysql> SELECT COALESCE(null, null, null, 'A', 'B');
Result: 'A'

mysql> SELECT COALESCE('A', 'B', null, 'C', 'D');
Result: 'A'

mysql> SELECT COALESCE(null, 1, 2, 3, null, 4);
Result: 1

mysql> SELECT COALESCE(null, 'techonthenet.com', 'checkyourmath.com');
Result: 'techonthenet.com'

mysql> SELECT COALESCE(null, null, null, null, null);
Result: NULL

Thank you!

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