Menu
Can someone explain how the COALESCE function in TSQL works? The syntax is as follows
COALESCE(x, y)
The MSDN document on this function is pretty vague
396
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE , you must provide a data type. ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.
The coalesce in MySQL can be used to return first not null value. If there are multiple columns, and all columns have NULL value then it returns NULL otherwise it will return first not null value. The syntax is as follows. SELECT COALESCE(yourColumnName1,yourColumnName2,yourColumnName3,.......
Coalesce is simpler than a CASE statement. It is very much like ISNULL which typically I see used more in code. However, coalesce does have some advantages, including: no truncation, it takes more than 2 parameters, and it is ANSI compliant!
The COALESCE expression returns the first non-null expression. If all expressions evaluate to NULL, then the COALESCE expression return NULL; Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .
I'm not sure why you think the documentation is vague.
It simply goes through all the parameters one by one, and returns the first that is NOT NULL.
COALESCE(NULL, NULL, NULL, 1, 2, 3)
=> 1
COALESCE(1, 2, 3, 4, 5, NULL)
=> 1
COALESCE(NULL, NULL, NULL, 3, 2, NULL)
=> 3
COALESCE(6, 5, 4, 3, 2, NULL)
=> 6
COALESCE(NULL, NULL, NULL, NULL, NULL, NULL)
=> NULL
It accepts pretty much any number of parameters, but they should be the same data-type. (If they're not the same data-type, they get implicitly cast to an appropriate data-type using data-type order of precedence.)
It's like ISNULL() but for multiple parameters, rather than just two.
It's also ANSI-SQL, where-as ISNULL() isn't.
94
I've been told that COALESCE is less costly than ISNULL, but research doesn't indicate that. ISNULL takes only two parameters, the field being evaluated for NULL, and the result you want if it is evaluated as NULL. COALESCE will take any number of parameters, and return the first value encountered that isn't NULL.
There's a much more thorough description of the details here http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/
38
Here is the way I look at COALESCE...and hopefully it makes sense...
In a simplistic form….
Coalesce(FieldName, 'Empty')
So this translates to…If "FieldName" is NULL, populate the field value with the word "EMPTY".
Now for mutliple values...
Coalesce(FieldName1, FieldName2, Value2, Value3)
If the value in Fieldname1 is null, fill it with the value in Fieldname2, if FieldName2 is NULL, fill it with Value2, etc.
This piece of test code for the AdventureWorks2012 sample database works perfectly & gives a good visual explanation of how COALESCE works:
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
