2.10.2.5.18. JPQL Subqueries
Subqueries may be used in the WHERE or HAVING clause.
The syntax for subqueries is as follows:
-
subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]
-
simple_select_clause ::= SELECT [DISTINCT] simple_select_expression
-
subquery_from_clause ::= FROM subselect_identification_variable_declaration {, subselect_identification_variable_declaration | collection_member_declaration }*
-
subselect_identification_variable_declaration ::= identification_variable_declaration | derived_path_expression [AS] identification_variable {join}* | derived_collection_member_declaration
-
simple_select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable
-
derived_path_expression ::= superquery_identification_variable.{single_valued_object_field.}*collection_valued_field | superquery_identification_variable.{single_valued_object_field.}*single_valued_object_field
-
derived_collection_member_declaration ::= IN superquery_identification_variable.{single_valued_object_field.}*collection_valued_field
Subqueries are restricted to the WHERE and HAVING clauses in this release.
Support for subqueries in the FROM clause will be considered in a later release of the specification.
Examples:
SELECT DISTINCT auth FROM Author auth
WHERE EXISTS (SELECT spouseAuth FROM Author spouseAuth WHERE spouseAuth = auth.spouse)
SELECT mag FROM Magazine mag
WHERE (SELECT COUNT(art) FROM mag.articles art) 10
Note that some contexts in which a subquery can be used require that the subquery be a scalar subquery (i.e., produce a single result). This is illustrated in the following example involving a numeric comparison operation.
SELECT goodPublisher FROM Publisher goodPublisher
WHERE goodPublisher.revenue (SELECT AVG(p.revenue) FROM Publisher p)
SELECT goodCustomer
FROM Customer goodCustomer
WHERE goodCustomer.balanceOwed (
SELECT AVG(c.balanceOwed)/2.0 FROM Customer c)