JPQL - EJB 3 in Action - page 427

If you want to retrieve the information from users that don't exist in the same static list,
then you can use this WHERE clause:
WHERE u.userId not IN ('viper', 'drdba', 'dumpster')
A subquery is a query within a query. A subquery may return a single value or multiple
values. You'll learn more about subqueries in section 11.1.4 . Let's review an example of a
subquery with an IN operator:
WHERE c.user IN (SELECT u
FROM User u
WHERE u.userType = 'A')
In this expression you're trying to evaluate the User field with a list of users retrieved by
the subquery. When a query contains a subquery, the subquery is executed first, and then
the parent query is evaluated against the result retrieved by the subquery.
Using the LIKE operator
The LIKE operator allows you to determine whether a single-value path expression
matches a string pattern. The syntax for the LIKE operator is
string_value_path_expression [NOT] LIKE pattern_value_
Here pattern_value is a string literal or an input parameter. The pattern_value
may contain an underscore ( _ ) or a percent sign ( % ). The underscore stands for a single
character. Consider the following clause:
WHERE c.itemName LIKE '_ike'
This expression will return TRUE when c.itemName has values such as mike , bike ,
and so forth. You should be able to extend this technique to embed a space into any search
string, effectively making the space a wildcard. If you search for a single space, it'll only
match a single character.
The percent sign ( % ) represents any numbers of characters. Whenever you want to search
for all Category entities with a name that starts with Recycle , use this WHERE clause:
WHERE c.categoryName LIKE 'Recycle%'