I have a query where a row is selected when field1 <> 10.

When field1 is null, this predicate is false!

According to my primitive maths, if null <> 10 = false, then 10 = null.

What is the rational for this?


The expression:

field1 <> 10

doesn't evaluate to false when field1 is NULL. It evaluates to unknown....which isn't true.

See IS NULL (Transact-SQL) and NULL and UNKNOWN (Transact-SQL) in the product documentation.

Related reading: How to Get NULLs Horribly Wrong in SQL Server by Robert Sheldon

| improve this answer | |

This is because a comparison to NULL actually results in unknown. NULL is the absence of data and so you are comparing a value against nothing and the result cannot be determined.

When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.

Taken from the documentation on MSDN.

| improve this answer | |
  • Please also specify this is a rdbms method of treating NULL. Other languages like C++ treat it differently. – clifton_h Sep 8 '18 at 19:12
  • 2
    @clifton_h the question is tagged as sql-server and t-sql – World Wide DBA Sep 8 '18 at 20:47
  • I know. But I just think it is worth understanding since an Application May work differently. Hence the “please”. Not required, but a difference to note. – clifton_h Sep 8 '18 at 20:49
  • 1
    also its defined by the SQL standard to be this way. – danblack Sep 9 '18 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.