IS NULL Operator

The IS NULL operator test whether the result of its input expression is SQL NULL. (SQL NULL is used when a non-JSON field is set to NULL.) IS NULL requires an input expression that returns a single item. If that single item is SQL NULL, then IS NULL returns true.

A table field can be NULL if it is explicitly set to NULL, or if the table field is simply not populated when you import data.

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. This means that IS NULL cannot be used to identify missing fields from JSON columns. Use the Exists Operator instead.

Note

IS NULL returns false for JSON fields which exist but are set to NULL. This is because for JSON data, NULL is the JSON NULL, not the SQL NULL.

IS NOT NULL can also be used. It is equivalent to:

NOT (IS NULL <expression>)

For an example of using IS NULL and IS NOT NULL, see Filtering results.