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 totrueandfalselogical expressions can also yieldunknown.
This is a consequence of SQL's support ofNULLto mark either absent or unknown data. Thus, ifNULL 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 beunknown:
NULL = NULL
NULL = 1
NULL
NULL > 1
Please note that the SQL standard does not specifyhow 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 is nulland 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 is not distinct from .
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 tounknown 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
