Microsoft SQL Server NULLs Is null / Is not null
Example
Since null is not a value, you can't use comparison operators with nulls.
To check if a column or variable holds null, you need to use is null:
DECLARE @Date date = '2016-08-03'
The following statement will select the value 6, since all comparisons with null values evaluates to false or unknown:
SELECT CASE WHEN @Date = NULL THEN 1
WHEN @Date <> NULL THEN 2
WHEN @Date > NULL THEN 3
WHEN @Date < NULL THEN 4
WHEN @Date IS NULL THEN 5
WHEN @Date IS NOT NULL THEN 6
Setting the content of the @Date variable to null and try again, the following statement will return 5:
SET @Date = NULL -- Note that the '=' here is an assignment operator!
SELECT CASE WHEN @Date = NULL THEN 1
WHEN @Date <> NULL THEN 2
WHEN @Date > NULL THEN 3
WHEN @Date < NULL THEN 4
WHEN @Date IS NULL THEN 5
WHEN @Date IS NOT NULL THEN 6
