COALESCE IN Sql

COALESCE in Sql Server

Click Tracker and Link Manager
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

Free Time Tracking to help you manage your business better
ADVERTISEMENT
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

Join instructors who have made over $338m in sales
ADVERTISEMENT

Rathna

I m Rathina kumaravel (Ramki) and i m a software Engineer in Chennai. I have achieved Bachelor of engineering in Computer Science. I’m a simple person who hides a thousand feelings behind the happiest smile.

You May Also Like

8 Things To Know When Creating Video For Youtube

Onedot

What are the Basic Preventive Measures that You Can Take?

Print Your Expressions and Connect Customers in Day to Day Life

Top 10 Reason to Host Your Website in Bluehost

Leave a Reply