IS NULL and IS NOT NULL Operators

Syntax

is_null_expression ::= condition_expression [IS [NOT] NULL]

condition_expression ::= 
   comparison_expression | exists_expression 
    | is_of_type_expression | in_expression

Semantics

The IS NULL operator tests whether the result of its input expression is NULL. If the input expression returns more than one item, an error is raised. If the result of the input expression is empty, IS NULL returns false. Otherwise, IS NULL returns true if and only if the single item computed by the input expression is NULL. The IS NOT NULL operator is equivalent to NOT (IS NULL cond_expr). NULL is explained in Table 2-2.

Example 6-41 IS NULL Operator

Select the id and last name of all users who do not have a known income.

SELECT id, lastName FROM users u
WHERE u.income IS NULL;