COALESCE in Sql Server

COALESCE IN Sql
Disclosure: We sometimes use affiliate links in our content. This won’t cost you anything but it helps us to offset our operative costs. Thanks for your support!
Get it done on Fiverr
ADVERTISEMENT

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

Output: Rahul

SELECT COALESCE('Gangully', NULL, 'Rahul', 'Sachin Tendulkar')

Output: Gangully

Concatenate many rows into a single text string using COALESCE function

Suppose you are having table row values like

Sachin
Micheal
Ronaldo
Lee
Obama

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?

koe-uh-less

Introduction to NULLIF, ISNULL, and COALESCE in SQL Server 2005/2008

You get how much for free at WPX
ADVERTISEMENT
Related Post
Also Read  Snippets - Rahul Dravid's Pataudi Memorial lecture
Leave a Reply

Your email address will not be published.