IS NULL Predicate

The IS NULL predicate determines whether an expression has the value NULL. The predicate evaluates to TRUE if the expression is NULL. If the NOT option is used, the predicate evaluates to TRUE if the expression is NOT NULL.

SQL syntax

{ColumnName | Constant | Expression | LOBDataType} IS [NOT] NULL

Parameters

Parameter	Description
`ColumnName`	The name of a column from which a value is to be taken. See "Names, Namespace and Parameters" for information on column names.
`Constant`	A specific value. See "Constants" for information on constants.
`Expression`	Expression to test.
`LOBDataType`	Value to test that is in a `CLOB`, `BLOB`, or `NCLOB` data type.

Examples

Use IS NULL to identify the president of the company, who is the only person without a manager.

Command> SELECT * FROM employees 
         WHERE manager_id IS NULL;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, 
<NULL>, <NULL>, 90 >
1 row found.

The following statement uses IS NULL to identify all locations without a state or province.

Command> SELECT * FROM locations
         WHERE state_province IS NULL;
< 1000, 1297 Via Cola di Rie, 00989, Roma, <NULL>, IT >
< 1100, 93091 Calle della Testa, 10934, Venice, <NULL>, IT >
< 1300, 9450 Kamiya-cho, 6823, Hiroshima, <NULL>, JP >
< 2000, 40-5-12 Laogianggen, 190518, Beijing, <NULL>, CN >
< 2300, 198 Clementi North, 540198, Singapore, <NULL>, SG >
< 2400, 8204 Arthur St, <NULL>, London, <NULL>, UK >
6 rows found.