Thursday, July 5, 2012

ISNULL + NULLIF instead of CASE WHEN

Difference between NULL and (Empty or ' ')
ISNULL() vs COALESCE()

ISNULL ( <check_expression>, <replacement_value>)
Replaces check_expression with the specified replacement_value, when check_expression is null

NULLIF(<expression>, <expression>)
  • Returns a null value if the two specified expressions are equal.
  • Returns the first expression if the two expressions are not equal.
  • ReturnType is the type of the first expression
  • The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

DECLARE @EmpID VARCHAR(50)
SELECT
       CASE
              WHEN @EmpID IS NULL
              THEN 'rkbabu'
              ELSE @EmpID
       END

SELECT ISNULL(NULLIF(@EmpID, NULL), 'rkbabu')

SELECT NULLIF(10,20) ------> 10
SELECT NULLIF(10, NULL) ------> 10
SELECT NULLIF(10, 10) -------> NULL
SELECT NULLIF('', '     ') ----------> NULL 
SELECT NULLIF('@', '@@') ------> @

SELECT NULLIF(NULL, 10) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.
SELECT NULLIF(NULL, NULL) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.

DECLARE @EmpID VARCHAR(50)
SELECT NULLIF(@EmpID, 10) ----------> NULL
SELECT NULLIF(@EmpID, @EmpID) ----------> NULL

SELECT CASE WHEN NULL = 10 THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN 10 = NULL THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN NULL = NULL THEN NULL ELSE 10 END ------> 10 Since one null not equal to another null

Consider some time we will use case in the where condition, this can be replaced with
ISNULL and NULLIF function
DECLARE @Column1 INT, @Column2 INT

SET @Column1 = -1;

SELECT *
FROM tableName
WHERE Column1 = CASE WHEN @Column1 = -1 THEN Column1  ELSE @Column1 END
AND Column2 = CASE WHEN @Column2 IS NOT NULL THEN @Column2 ELSE Column2 END

SELECT *
FROM tableName
WHERE Column1 = ISNULL(NULLIF(@Column1, -1), Column1)
AND Column2 = ISNULL(NULLIF(@Column2, NULL), Column2)

No comments: