Contents Index Unknown Values: NULL Properties of NULL

ASA SQL User's Guide
  Queries: Selecting Data from a Table
    The WHERE clause: specifying rows
      Unknown Values: NULL

Testing a column for NULL

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 unit_price is less than $15 or is NULL:

SELECT quantity , unit_price
FROM product
WHERE unit_price < 15
OR unit_price 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.


Contents Index Unknown Values: NULL Properties of NULL