Overview:
This article explains the detailed difference between IsNull, Coalesce and NullIf.
ISNULL: This function works like “if” condition which we use in other program languages. It takes only two arguments, if first argument is Null then it returns second and one more thing is that the second argument should be of same size that means if first argument has varchar(2) and second has varchar(4) then it truncates last characters.
Below gives you the structure and the example
ISNULL(argument1, argument2)
Pseudocode
IF argument1 IS Null
RETURNS argument2
ELSE
RETURNS argument1
END
Example: SELECT ISNULL(NULL,'Test')
Result: Test
Example :
DECLARE @a VARCHAR(2);
DECLARE @b VARCHAR(4);
SET @b = 'abcd';
SELECT ISNULL(@a,@b)
Result : ab
COALESCE: This function works same as “if else” statement and it takes multiple arguments and it checks Null values for each if first argument is null then it considers the second value, if the both first and second arguments are nulls then it considers third argument. It contradicts the IsNull as it truncates the extra characters if the size of second argument is bigger than first but COALESCE doesn’t. Below are its structure and example
COALESCE(argument1, argument2, argument3[, argument4, argument6, argument7…])
Pseudocode
IF argument1 IS NOT Null
RETURNS argument1
RETURNS argument1
ELSE argument1 IS Null AND argument2 IS Null AND argument3 IS Null
RETURNS “Default Set Value”
END
Example:
Query:
SELECT field1,
field2,
field3,
COALESCE(field1,field2,field3,0) AS Result
FROM TableName
Result:
Example :
DECLARE @a VARCHAR(2);
DECLARE @b VARCHAR(4);
SET @b = 'abcd';
SELECT COALESCE (@a,@b)
Result : abcd
NULLIF(argument1, argument2)
Pseudocode
IF argument1 equals argument1
RETURNS Null
ELSE
RETURNS argument1
END
Example: SELECT NullIF(2,1)
Result: 2
Example: SELECT NullIF(1,1)
Result: NULL
Thank you for sharing this great article. Game Development Tutorials
ReplyDeleteThanks for sharing such an amazing Article! Download GTA 5 for PC
ReplyDeleteGreat Tutorial! Really Helpful - Download Tekken 6 for pc
ReplyDelete