JPQL - EJB 3 in Action - page 437

11.1.4. Ordering results
A subquery is a query inside a query. You use a subquery in a WHERE , HAVING , or GROUP
BY clause to filter the result set. Unlike SQL subqueries, EJB 3 subqueries aren't supported
in the FROM clause. If you have a subquery in a JPQL query, the subquery will be evaluated
first, and then the main query will be retrieved based on the result of the subquery.
Here's the syntax for the subquery:
[NOT] IN / [NOT] EXISTS / ALL / ANY / SOME (subquery)
From the syntax of the language, it's clear that you can use IN , EXISTS , ALL , ANY , or
SOME with a subquery.
Let's look at some examples of subqueries in more detail.
Using IN with a subquery
We've already discussed using the IN operator when a single-value path expression is eval-
uated against a list of values. You can use a subquery to produce a list of results:
SELECT i
FROM Item i
WHERE i.user IN (SELECT c.user
FROM Category c
WHERE c.categoryName LIKE :name)
In this query, first the subquery (in parentheses) is executed to retrieve a list of users, and
then the i.item path expression is evaluated against the list.
Using EXISTS
EXISTS (or NOT EXISTS ) tests whether the subquery contains any result set. It returns
TRUE if the subquery contains at least one result and FALSE otherwise. Here's an example
illustrating the EXISTS clause:
SELECT i
FROM Item i
WHERE EXISTS (SELECT c