what is the use of coalesce in sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what is the use of coalesce in sql?

Author

Topic

ammuhere
Starting Member

8 Posts

Posted - 2013-10-04 : 23:01:21

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?

jethrow
Starting Member

37 Posts

Posted - 2013-10-05 : 03:12:57

Note that a zero-length string does not equal NULL (unknown). COALESCE returns the first non-null parameter. Concatenating a zero-length string with a second string will result in the second string. Concatenating NULL with a second string will result in NULL.

On a side note - I'd recommend using COALESCE over ISNULL. SQL Svr ISNULL, imo, is somewhat confusing if you have a VBA background.

Microsoft SQL Server Noobie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:32:41

The main reason is because just when you declare a variable its value defaults to NULL.
So as specified in previous post concatenation with it will result in NULL value.

You can do below though in whih case you can keep code same as in .NET


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

    SELECT @DepartmentName AS DepartmentNames

This is because in above case you're explicitly initialising it to blank ('') before the concatenation step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Robowski
Posting Yak Master

101 Posts

Posted - 2013-10-07 : 08:43:21

quote:
Originally posted by ammuhere

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

COALESCE acts slightly different to ISNULL in 2 different ways. ISNULL can only replaces with one value where as COALESCE replaces to the n'th.

ISNULL will always retain the target column not null constraint if being used to SELECT INTO statement to create a new table where as COALESCE can be used to manipulate the new column to be all NULL marks.

Run the below and then look at the different column constraints:

IF OBJECT_ID ('RobNullTest', 'U') IS NOT NULL
DROP TABLE RobNullTest;
GO

CREATE TABLE RobNullTest
(Col1 INT UNIQUE IDENTITY(1,1) NOT NULL, Col2 varchar(20) NOT NULL)

INSERT INTO RobNullTest (Col2)
VALUES ('Value');
GO

IF OBJECT_ID ('RobNullTestISNULL', 'U') IS NOT NULL
DROP TABLE RobNullTestISNULL;
GO

SELECT
Col1
,ISNULL(Col2, NULL) as Col2
INTO
RobNullTestISNULL
FROM
RobNullTest;
GO

IF OBJECT_ID ('RobNullTestCOLL', 'U') IS NOT NULL
DROP TABLE RobNullTestCOLL;
GO

SELECT
Col1
,COALESCE(Col2, NULL) as Col2
INTO
RobNullTestCOLL
FROM
RobNullTest;
GO

/* Run to tidy up

IF OBJECT_ID ('RobNullTest', 'U') IS NOT NULL
DROP TABLE RobNullTest;
GO

IF OBJECT_ID ('RobNullTestISNULL', 'U') IS NOT NULL
DROP TABLE RobNullTestISNULL;
GO

IF OBJECT_ID ('RobNullTestCOLL', 'U') IS NOT NULL
DROP TABLE RobNullTestCOLL;
GO

*/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-07 : 13:48:32

I'd also recommend using COALESCE over ISNULL, but they each have slight differences in behavior. So, I'd suggest you do a search for the differences. Here is a link to one such discussion:
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources