Search

7.11. Subqueries

download PDF
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.
Red Hat logoGithubRedditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

© 2024 Red Hat, Inc.