Is it necessary to test for NULL if also testing for greater than?

Question

I inherited some old stored procedures today, and came across several examples that followed this general pattern, where @Test is some INT value:

IF @Test IS NOT NULL AND @Test > 0
    -- do something...

My understanding is that if @Test is NULL, then it has no value, and is not greater than, less than or even equal to zero. Therefore, testing for NULL is redundant in the above code:

IF @Test > 0
    -- do something...

This second version seems to work just fine, and is far more readable IHMO.

So, my question: Is my understanding of NULL being unnecessary in this instance correct, or is there some obvious use-case I'm overlooking here where it could all go horribly wrong?

Note: In some cases, it was obvious that the intent was checking for the existence of a value, and I've changed those to IF EXISTS... my question is more concerned with the general case outlined above.

Answer 1

Compare with NULL is necessary if you use ELSE statements:

for example:

declare @t int
set @t=null
if (@t>0) print '1' -- works fine
if (@t<0) print '2' --works fine

if (@t>0) 
    print '3' --works fine
else print '4' --here we start getting problems, because we are sure that @t<=0 that is obviously not true

Answer 2

In SQL all comparisons to a NULL value evaluate to false. So you always have to check explicitly for NULL, if you wish to act on it. So, in this case, the additional test is not necessary.

Answer 3

@FlorianHeer is right on. NULL > 0 will eventually evaluate to false but as @Pred points out that is because Null > 0 actually evaluates to null and null cast to a bit is false....

A null is an unknown and therefore any comparison with it is also unknown. Think of arithmetic functions such as addition 1 + NULL = NULL, or concatenation 'A' + NULLL = NULL. NULL means the SQL database engine cannot interpret what its value is so any function or comparison on it is also unknown.

@MikkaRin pointed out that it is the assumption in the ELSE portion of a case statement or IF statement where that can become problematic but lets also think about this in the context of a join and how you may or may not want to see the results.

DECLARE @Table1 AS TABLE (Col INT)
DECLARE @Table2 AS TABLE (Col INT)
INSERT INTO @Table1 VALUES (1),(2),(3)
INSERT INTO @Table2 VALUES (1),(NULL),(3),(4)

SELECT *
FROM
    @Table1 t1
    INNER JOIN @Table2 t2
    ON t1.Col <> t2.Col

Naturally you might think because NULL would be not equal to 1,2,3 that it should be included in the result set. But null is unknown so SQL is saying well I don't know if NULL could be 1,2,3 so I cannot return that as a result.

Now lets do the same thing but add a NULL in the first table:

DECLARE @Table1 AS TABLE (Col INT)
DECLARE @Table2 AS TABLE (Col INT)
INSERT INTO @Table1 VALUES (1),(2),(3),(NULL)
INSERT INTO @Table2 VALUES (1),(NULL),(3),(4)

SELECT *
FROM
    @Table1 t1
    INNER JOIN @Table2 t2
    ON t1.Col = t2.Col

Again you might think that NULL is = to NULL but any comparison of NULL is considered unknown so even though both tables have NULL in it it will not be returned in the dataset.

Now consider:

DECLARE @Table1 AS TABLE (Col INT)
INSERT INTO @Table1 VALUES (1),(2),(3),(NULL)

SELECT *, CASE WHEN Col < 2 THEN Col ELSE 1000 END as ColCase
FROM
    @Table1 t1

Which will make even the NULL 1000 the question is should NULL an unknown be 1000? if NULL is unknown how do we know that it isn't less than 2?

For a lot of your operations it may simply be enough to compare @Value > 1 but especially when you start dealing with ELSE in case of IF statements or joining on the antithesis you should consider dealing with the NULLs. Such as using ISNULL() or COALESCE() as @GuidoG points out.

IMHO being explicit about your intentions during operations to appropriately account for null values out weighs the minimal savings of typing.

Answer 4

you could replace it with

if isnull(@test, 0) > 0

This way it will be shorter and you still have checked everything

Answer 5

another interesting example:

SELECT (null > 0) AS a, !(null > 0) AS b

value of both a and b will be NULL