Definition of COALESCE
COALESCE returns the first non-null expression among its arguments.
COALESCE is one of the most useful function in sql. I have used it many times especially to concatenate many rows into a single text. We can also use ISNULL for similar task but COALESCE is more efficient.
Difference between ISNULL and COALESCE
- ISNULL is limited to two arguments but in COALESCE we can use more number of arguments
- Performancewise COALESCE is faster than ISNULL
- COALESCE is ANSI SQL standard whereas ISNULL is a proprietary TSQL function
- Using select ISNULL(NULL, NULL) doesn’t throw error but select COALESCE(NULL, NULL) throw error
COALESCE Sample Examples
SELECT COALESCE(NULL, NULL, NULL, 'Sachin Tendulkar')
Output: Sachin Tendulkar
SELECT COALESCE(NULL, NULL, 'Rahul', 'Sachin Tendulkar')
SELECT COALESCE('Gangully', NULL, 'Rahul', 'Sachin Tendulkar')
Concatenate many rows into a single text string using COALESCE function
Suppose you are having table row values like
use this below query
DECLARE @CONSTR VARCHAR(MAX) SELECT @CONSTR = COALESCE(@CONSTR +',' ,'') + Name FROM VIPTABLE SELECT @CONSTR
Output : Sachin,Micheal,Ronaldo,Lee,Obama
How to pronounce COALESCE?
Introduction to NULLIF, ISNULL, and COALESCE in SQL Server 2005/2008