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-58 Select the id, first name, and last name of all users who do not have a known income

Consider an application that maintains the user data. See users table in the Tables used in the Examples topic.

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

Explanation: Assuming that a NULL value is populated in the user table if a user does not have any known income, in the above query, you fetch the details of users whose income field has a NULL value.

Output:

{"id":40,"firstName":"Joanna","lastName":"Smith"}

Example 6-59 Fetch the ticket number, the full name of passengers from the airline baggage tracking application whose checked bag has a tag associated with it

Consider the airline baggage tracking application. See BaggageInfo table in the Tables used in the Examples topic.

SELECT ticketNo,fullname
FROM BaggageInfo bag
WHERE EXISTS bag.bagInfo.tagNum [$element IS NOT NULL]

Explanation: In the airline baggage tracking application, there is a unique tag number associated with every checked bag carried by the passenger. In this query, you fetch the details of passengers who have a tag number, which means the tagNum field in the bagInfo table is not null. You use the EXISTS operator to verify whether or not the tagNum field includes a NULL value.

Output:

{"ticketNo":1762330498104,"fullname":"Michelle Payne"}
{"ticketNo":1762340683564,"fullname":"Mary Watson"}
{"ticketNo":1762377974281,"fullname":"Kendal Biddle"}
{"ticketNo":1762320569757,"fullname":"Lucinda Beckman"}
{"ticketNo":1762392135540,"fullname":"Adelaide Willard"}
{"ticketNo":1762399766476,"fullname":"Raymond Griffin"}
{"ticketNo":1762324912391,"fullname":"Elane Lemons"}
{"ticketNo":1762390789239,"fullname":"Zina Christenson"}
{"ticketNo":1762340579411,"fullname":"Zulema Martindale"}
{"ticketNo":1762376407826,"fullname":"Dierdre Amador"}
{"ticketNo":176234463813,"fullname":"Henry Jenkins"}
{"ticketNo":1762311547917,"fullname":"Rosalia Triplett"}
{"ticketNo":1762320369957,"fullname":"Lorenzo Phil"}
{"ticketNo":1762341772625,"fullname":"Gerard Greene"}
{"ticketNo":1762344493810,"fullname":"Adam Phillips"}
{"ticketNo":1762355527825,"fullname":"Doris Martin"}
{"ticketNo":1762383911861,"fullname":"Joanne Diaz"}
{"ticketNo":1762348904343,"fullname":"Omar Harvey"}
{"ticketNo":1762350390409,"fullname":"Fallon Clements"}
{"ticketNo":1762355854464,"fullname":"Lisbeth Wampler"}
{"ticketNo":1762357254392,"fullname":"Teena Colley"}

21 rows returned