7.11. Subqueries

For databases that support subselects, EJB-QL supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. 
select fatcat from Cat as fatcat 
where fatcat.weight > ( 
    select avg(cat.weight) from DomesticCat cat 
)
select cat from DomesticCat as cat 
where cat.name = some ( 
    select name.nickName from Name as name 
)
select cat from Cat as cat 
where not exists ( 
    from Cat as mate where mate.mate = cat 
)
select cat from DomesticCat as cat 
where cat.name not in ( 
    select name.nickName from Name as name 
)
For subqueries with more than one expression in the select list, you can use a tuple constructor: 
select cat from Cat as cat 
where not ( cat.name, cat.color ) in ( 
    select cat.name, cat.color from DomesticCat cat 
)
Note that on some databases (but not Oracle or HSQLDB), you can use tuple constructors in other contexts, for example when querying components or composite user types: 
select cat from Person where name = ('Gavin', 'A', 'King')
Which is equivalent to the more verbose: 
select cat from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')
There are two good reasons you might not want to do this kind of thing: first, it is not completely portable between database platforms; second, the query is now dependent upon the ordering of properties in the mapping document.