Subqueries may be used in the WHERE or HAVING clause of a query. Subqueries must be surrounded by parentheses.
The following example find all customers who have placed more than 10 orders:
SELECT c FROM Customer c WHERE (SELECT COUNT(o) FROM c.orders o) > 10
The [NOT] EXISTS expression is used with a subquery, and is true only if the result of the subquery consists of one or more values and is false otherwise.
The following example finds all employees whose spouse is also an employee:
SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ( SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = emp.spouse)
The ALL expression is used with a subquery, and is true if all the values returned by the subquery are true, or if the subquery is empty.
The ANY expression is used with a subquery, and is true if some of the values returned by the subquery are true. An ANY expression is false if the subquery result is empty, or if all the values returned are false. The SOME keyword is synonymous with ANY.
The ALL and ANY expressions are used with the =, <, <=, >, >=, <> comparison operators.
The following example finds all employees whose salary is higher than the salary of the managers in the employee’s department:
SELECT emp FROM Employee emp WHERE emp.salary > ALL ( SELECT m.salary FROM Manager m WHERE m.department = emp.department)