JPQL Subqueries

With JPQL the user has a very flexible query syntax which allows for querying of the vast majority of data components in a single query. In some situations it is desirable for the query to utilise the results of a separate query in its calculations. JPQL also allows the use of subqueries. Here's an example

SELECT Object(e) FROM org.datanucleus.Employee e 
WHERE e.salary > (SELECT avg(f.salary) FROM org.datanucleus.Employee f)

So we want to find all Employees that have a salary greater than the average salary. The subquery must be in parentheses (brackets). Note that we have defined the subquery with an alias of "f", whereas in the outer query the alias is "e".



All or Any Expressions

One use of subqueries with JPQL is where you want to compare with some or all of a particular expression. To give an example

SELECT emp FROM Employee emp 
WHERE emp.salary > ALL (SELECT m.salary FROM Manager m WHERE m.department = emp.department)

So this returns all employees that earn more than all managers in the same department! You can also compare with some/any, like this

SELECT emp FROM Employee emp 
WHERE emp.salary > ANY (SELECT m.salary FROM Manager m WHERE m.department = emp.department)

So this returns all employees that earn more than any one Manager in the same department.

Existence Expressions

Another use of subqueries in JPQL is where you want to check on the existence of a particular thing. For example

SELECT DISTINCT emp FROM Employee emp
WHERE EXISTS (SELECT emp2 FROM Employee emp2 WHERE emp2 = emp.spouse)

So this returns the employees that have a partner also employed.