JPQL - EJB 3 in Action - page 429

Table 11.4. Truth table of Boolean value compared with null

Expression 1 value Boolean operator Expression 2 value Result

TRUE AND null UNKNOWN

FALSE AND null FALSE

NULL AND null UNKNOWN

TRUE OR null TRUE

NULL OR null UNKNOWN

FALSE OR null UNKNOWN

NOT null UNKNOWN

You can use the IS NULL or IS NOT NULL operator to check whether a single-value
path expression contains null or not-null values. If a single-value path expression contains
null, then IS NULL will return TRUE and IS NOT NULL will return FALSE . If you want
to determine whether the single-value path expression isn't null, use the following WHERE
clause:

WHERE c.parentCategory IS NOT NULL

You can't use the IS NULL expression to compare a path expression that's of type collec-
tion; in other words, IS NULL will not detect whether a collection type path expression is
an empty collection. JPQL provides the IS [NOT] EMPTY comparison operator to check
whether a collection type path expression is empty. The following WHERE clause will work
when you want to retrieve all category entities that don't have any items:

WHERE c.items IS EMPTY

As we explained earlier, JPQL statements are translated to SQL statements by the persist-
ence provider. There's no equivalent of the IS EMPTY clause in SQL. So you must be
wondering what SQL statement is generated when IS EMPTY is used. The IS EMPTY
clause is used with a collection-valued path expression that's typically an association field,
and therefore the generated SQL statement will be determining whether the JOIN for the
association retrieves any record in a subquery. To clarify, let's examine this JPQL query:

SELECT c
FROM Category c
WHERE c.items IS EMPTY