Subqueries can be used in the WHERE or HAVING clause. A subquery is a SELECT statement within parentheses which in turn is part of a conditional expression. For example, the query

SELECT a FROM Account a WHERE a.balance >
(SELECT MIN(a2.balance) FROM Account a2) )

will retrieve those accounts which have a balance greater than the minimum balance. Note we use a different indicator variable, a2, in the subquery to distinguish it from the indicator variable, a, in the main query.

An EXISTS expression is true if the result of the subquery consists of one or more values. For example, the query

SELECT c FROM Customer c WHERE EXISTS
(SELECT a2 FROM Account a2 WHERE a2.customer = c
AND a2.accountType='S')

retrieves customers who have at least one savings account.

An ALL expression is true if the conditional expression is true for all values in the subquery result. For example, the query

SELECT c FROM Customer c WHERE c.monthlyIncome >
ALL (SELECT a.balance FROM c.accounts a)

retrieves...