Comparing a value to a NULL in t-SQL

Question

I was curious if it's legal in t-SQL to compare a NULL to a value?

For instance, if I have:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE ctx.n1 < 130

the WHERE clause in that case is always evaluated as FALSE. Is it something I can rely on?

Answer 1

You can't compare NULL with any other value, it will result in 'UNKNOWN'.

From msdn source

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Answer 2

All boolean operations in T-Sql with null value returns 'UNKNOWN', which is recognized as false in clauses. You can use ISNULL function when you want set some default value. for example in your case:

WITH ctx AS(SELECT 123 AS n0, NULL AS n1)
SELECT n0 FROM ctx
WHERE isnull(ctx.n1,0) < 130