|
You can use IS NULL in search conditions to compare column values to NULL and to select them or perform a particular action based on the results of the comparison. Only columns that return a value of TRUE are selected or result in the specified action; those that return FALSE or UNKNOWN do not.
The following example selects only rows for which UnitPrice is less than $15 or is NULL:
SELECT Quantity, UnitPrice FROM Products WHERE UnitPrice < 15 OR UnitPrice IS NULL;
The result of comparing any value to NULL is UNKNOWN, since it is not possible to determine whether NULL is equal (or not equal) to a given value or to another NULL.
There are some conditions that never return true, so that queries using these conditions do not return result sets. For example, the following comparison can never be determined to be true, since NULL means having an unknown value:
WHERE column1 > NULL
This logic also applies when you use two column names in a WHERE clause, that is, when you join two tables. A clause containing the condition
WHERE column1 = column2
does not return rows where the columns contain NULL.
You can also find NULL or non-NULL with this pattern:
WHERE column_name IS [NOT] NULL
For example:
WHERE advance < $5000 OR advance IS NULL
For more information, see NULL value.