NULLIF,ISNULL,COALESCE

NULLIF,ISNULL,COALESCE

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

ISNULL (expr_check, expr_replace)

Returns a value that indicates whether the value of the specified column is null.


COALESCE (expr1...exprn)

Returns first non-null expression among its arguments
If all arguments are NULL, COALESCE returns NULL.
Rows in a table can be converted into columns using COALESCE.
Please see the below example.

Here we GO!!!!

DECLARE @table TABLE (id INT IDENTITY(1,1),FirstName VARCHAR(50),
LastName VARCHAR(50),
Current_income NUMERIC(19,2),
Previous_income NUMERIC(19,2))

INSERT INTO @table VALUES('Karthik','M',null,null)
INSERT INTO @table VALUES('Suman','PV',null,20.00)
INSERT INTO @table VALUES('First','Last',30.25,null)

Example for NULLIF:
SELECT id,FirstName+' '+LastName AS FullName,
NULLIF(current_income,Previous_income) AS Income
FROM @table



Example for ISNULL:
SELECT id,FirstName+' '+LastName AS FullName,
ISNULL(current_income,Previous_income) AS Income
FROM @table


Example for COALESCE:
SELECT id,FirstName+' '+LastName AS FullName,
COALESCE(current_income,Previous_income) AS Income
FROM @table


Example for ISNULL Combined with COALESCE:
SELECT id,FirstName+' '+LastName AS FullName,
ISNULL(coalesce(current_income,Previous_income),0.00) AS Income
FROM @table


Example to convert rows into columns using COALESCE:
DECLARE @concat varchar(300)
SELECT @concat = COALESCE(@concat+', ','')+FirstName FROM @table
WHERE current_income IS NULL
SELECT @concat as Names