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
NULLmeans the data value for the column is unknown or not available.
NULLis 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 (
NULLfor 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 |
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.