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)