CODEWITHSUNDEEP
sqlsql-server-2008-r2
Tom Cruise
Tom Cruise

Reputation: 1425

what is the use of coalesce in sql?

I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.

I came across a piece of code posted in the forum about the different uses of coalesce.

use adventureworks

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' 
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

which return the result in a single line.

So why sql does not support string concatenation by default as in .NET like below?

DECLARE @DepartmentName VARCHAR(1000)

    **SELECT @DepartmentName = @DepartmentName + Name + ';'**
    FROM HumanResources.Department
    WHERE (GroupName = 'Executive General and Administration')

    SELECT @DepartmentName AS DepartmentNames

what is the use of coalesce in the below line

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'

and why 

**SELECT @DepartmentName = @DepartmentName + Name + ';'**
    FROM HumanResources.Department
    WHERE (GroupName = 'Executive General and Administration')

is not working?

Upvotes: 0

Views: 3509

Answers (4)

Rajesh Paul
Rajesh Paul

Reputation: 7019

coalesce simply returns the value of the 1st non-NULL argument in its parameter list.

e.g.

1.select coalesce(NULL,0,2,1);

will return 0 as 0 doesn't mean NULL.

2.select coalesce(id,emp_id,0) from tab1;

In this case attribute-id will be returned if it is NOT NULL otherwise if emp_id is NOT NULL emp_id will be returned else 0 will be returned.

In this case for concatenation you can simply use either +-operator or concat()-function. But here coalesce is used for the case when DepartmentName=NULL because if you concatenate or do some operation with NULL the result will be NULL. Hence to use blank(i.e. '') in place of NULL coalesce() has been used.

In this case coalesce has been used as DepartmentName is NULL at the moment when it is declared. Use the syntax Moho has given in his answer to substitute the use of coalesce.

Upvotes: 3

Moho
Moho

Reputation: 16553

add set @DepartmentName = '' after DECLARE @DepartmentName VARCHAR(1000) and you won't need the coalesce statement.

edit: updated after comment clarified things for me

Upvotes: 0

fan711
fan711

Reputation: 716

COALESCE returns from a list of arguments the first expression that does not evaluate to NULL.

For your example COALESCE(@DepartmentName,'') this means that if #DepartmentName is NULL it shall return '' (an empty string) instead. This is needed because concatenating strings with a null value would return NULL.

SELECT 'test' + NULL -- will return NULL

COALESCE (Transact-SQL)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425398

COALESCE() returns the first non-null term. This code:

COALESCE(@DepartmentName,'')

means return a blank if the department name is null.

Concatenating a null with something gives a null, so if you don't use COALESCE() you'd lose the name value too.

Upvotes: 0

Related Questions