Exists Operator

Syntax

exists_expression ::= EXISTS concatenate_expression

Semantics

The exists operator checks whether the sequence returned by its input expression is empty or not, and returns false or true, respectively. A special case is when the input expression returns NULL. In this case, EXISTS will also return NULL, unless it is known that the input expression will always return at least one item, in which case EXISTS returns true.

Example 6-83 Exists Operator

Find all the users who do not have a zip code in their addresses.

SELECT id FROM users u
WHERE NOT EXISTS u.address.zip

Notice that the above query does not select users whose zip code has the json null value. The following query includes those users as well.

SELECT id FROM users u
WHERE NOT EXISTS u.address.zip OR u.address.zip = null

What if the Users table contains a row R whose address column is NULL? In general, SQL for Oracle NoSQL Database interprets NULL as an unknown value, rather than an absent value. So, in row R, the address is unknown, and as a result, we don’t know what its zip code is or if it even has a zip code. In this case, the expression u.address.zip will return NULL on R and exists u.address.zip will also return NULL, which implies that row R will not be selected by the above queries. On the other hand, row R will be selected by the following query. In this case, we know that every row does have an address, even though the address may be unknown (i.e., NULL) in some rows. So, even though the expression u.address returns NULL, exists u.address return true.

SELECT id FROM users u
WHERE EXISTS u.address