NULL behavior in three valued logic

SQL uses three valued logic (true, false, or unknown). This is important when dealing with NULL as a query may not return the desired results.

This MSDN article provides an excellent definition of NULL:

The value NULL means the data value for the column is unknown or not available. NULL is not synonymous with zero (numeric or binary value), a zero-length string, or blank (character value). Rather, null values allow you to distinguish between an entry of zero (numeric columns) or blank (character columns) and a nonentry (NULL for both numeric and character columns).

Let’s illustrate this point with the test table below.

CREATE TABLE tbl (
  value varchar(6))
 
INSERT INTO tbl VALUES
('apple'), ('banana'), (NULL)

When we fetch all the results, the NULL is returned.

SELECT value FROM tbl

|  VALUE |
----------
|  apple |
| banana |
| (null) |

When a predicate is specified (to exclude apples), the NULL is not returned.

SELECT value FROM tbl WHERE value <> 'apple'

|  VALUE |
----------
| banana |

The NULL is not returned here because of the way it is evaluated. Since the value is not known it can’t be logically compared against any other value:

(null) <> 'apple'

It is neither true or false and it is therefore considered unknown. It must then be explicitly specified in the predicate:

SELECT value FROM tbl WHERE value <> 'apple' OR value IS NULL

|  VALUE |
----------
| banana |
| (null) |

This behavior can be disabled in SQL Server by setting ANSI_NULLS to off. However, disabling ANSI_NULLS is not recommended as it will always be on in future versions.