database concepts

Using a database: multiple table queries

NULL values in queries

NULL is unlike any other value.

Conditions in SQL can actually have three values: TRUE, FALSE and UNKNOWN. Any value compared to NULL will always have the value UNKNOWN. NULL represents a lack of data so a NULL cannot be equal or unequal to any value or to another NULL.

For a row to be included in the output of a SELECT query, the condition must evaluated to TRUE.

For example, take the condition:
     ItemPrice = 600
Then the condition is:

• TRUE if the price is $600
• FALSE if the price is any other dollar value than $600
• UNKNOWN if the price is NULL

To test for NULLs, use only the comparison operators IS NULL and IS NOT NULL.

The following extract from the Oracle manual provides more information on NULL.