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".
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.
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.
|
