JPQL - EJB 3 in Action - page 438

FROM Category c
WHERE c.user = i.user)
If you look carefully at the result of this subquery, you'll notice that it's the same as the
query example used in the previous section with the IN operator. An EXISTS clause is
generally preferred over IN , particularly when the underlying tables contain a large number
of records. This is because databases typically perform better when using EXISTS . Again,
this is due to the work of the query processor translating JPQL queries into SQL by the
persistence provider.
Using ANY, ALL, and SOME
Using the ANY , ALL , and SOME operators is similar to using the IN operator. You can use
these operators with any numeric comparison operators, such as = , > , >= , < , <= and <> .
Here's an example of a subquery demonstrating the ALL operator:
SELECT c
FROM Category c
WHERE c.createDate >= ALL
(SELECT i.createDate
FROM Item i
WHERE i.user = c.user)
If you include the ALL predicate, the subquery returns TRUE if all the results retrieved by
the subquery meet the condition; otherwise, the expression returns FALSE . In the example
the subquery returns FALSE if any item in the subquery has a createDate later than the
createDate for the category in the main query.
As the name suggests, if you use ANY or SOME , the expression returns TRUE if any of the
retrieved results meet the query condition. You can use ANY in a query as follows:
SELECT c
FROM Category c
WHERE c.createDate >= ANY
(SELECT i.createDate
FROM Item i
WHERE i.seller = c.user)