COALESCE ( expression [ ,...n ] )

Coalesce is Equivalent with

CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 … ELSE expressionN END
END

My little experiment with coalesce

DECLARE @i VARCHAR(20)
DECLARE @exp2
SET @exp2 = NULL

SET @i = null
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [It’s Null]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [ more;]
SELECT COALESCE (@i , @exp2, ‘Stranger’) –result [Stranger]

SET @i = ‘Something’
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [Something more;]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [Something]
SELECT COALESCE (@i , @exp2 , ‘Stranger’) –result [Something]

As you see if I use case and combine it with null function it’ll need more than one line. Coalesce is very useful and helpful in above case.