Handling NULL Values in Databases
In relational databases a NULL value is used to represent a missing or unknown value in the column. The ANSI SQL-92 specification defines the handling of the NULL value in databases.
SQL Ternary Logic
SQL relies on a three valued logic. Additionally to true and false logical expressions can also yield unknown.
This is a consequence of SQL's support of NULL to mark either absent or unknown data. Thus, if NULL is part of a logical expression the outcome will be NULL. In more colloquial terms NULL means "it could have any value".
Therefore, in all the below cases the result will be unknown:
NULL = NULL
NULL = 1
NULL <> 1
NULL > 1
Please note that the SQL standard does not specify how NULL values should be ordered in comparison with non-NULL values.
Testing for NULL
From the above it emerges that a specific SQL command is needed to check whether a value is indeed NULL.
SQL has the <expression> is null and <expression> is not null expressions to ascertain an expression is NULL or not.
The SQL standard also defines an expression to compare two NULL values treating them the same. However, not all SQL flavors support it and may instead have their own proprietary alternative. The SQL standard specifies <expression> is not distinct from <expression>.
Where and Having Clauses
A Where clause needs to be true for the SQL query to execute.
Therefore if the condition in the Where clause will evaluate to unknown it will reject all rows.
For instance
SELECT c_columns
FROM t_table
WHERE c_columns = NULL
will reject all columns. In order to use NULL in Where clauses WHERE c_columns is NULL should be used instead.
It is important to note that since NULL is neither true nor false, but rather unknown in SQL logic the below query will never return NULL values.
SELECT c_columns
FROM t_table
WHERE c_columns = 'ANY VALUE'
Additional Resource
