Subqueries

Subqueries may be used in the WHERE or HAVING clause of a query. Subqueries must be surrounded by parentheses.

The following example finds all customers who have placed more than ten orders:

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

Subqueries may contain EXISTS, ALL, and ANY expressions.

• EXISTS expressions: 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 spouses are also employees:

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

• ALL and ANY expressions: 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 =, <, <=, >, >=, and <> comparison operators.

  The following example finds all employees whose salaries are higher than the salaries 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)