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