2.10.2.8. JPQL SELECT Clause

The SELECT clause denotes the query result. More than one value may be returned from the SELECT clause of a query.

The SELECT clause can contain one or more of the following elements: a single range variable or identification variable that ranges over an entity abstract schema type, a single-valued path expression, a scalar expression, an aggregate expression, a constructor expression.

The SELECT clause has the following syntax:

  • select_clause ::= SELECT [DISTINCT] select_item {, select_item}*

  • select_item ::= select_expression [ [AS] result_variable]

  • select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable | OBJECT(identification_variable) | constructor_expression

  • constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}*)

  • constructor_item ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable

  • aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_object_path_expression)

For example:

SELECT pub.id, pub.revenue
    FROM Publisher pub JOIN pub.magazines mag WHERE mag.price  5.00

In the following example, videoInventory is a Map from the entity Movie to the number of copies in stock:

SELECT v.location.street, KEY(i).title, VALUE(i)
FROM VideoStore v JOIN v.videoInventory i
WHERE v.location.zipcode = '94301' AND VALUE(i)  0

Note that the SELECT clause must be specified to return only single-valued expressions. The query below is therefore not valid:

SELECT mag.authors FROM Magazine AS mag

The DISTINCT keyword is used to specify that duplicate values must be eliminated from the query result.

If DISTINCT is not specified, duplicate values are not eliminated.

The result of DISTINCT over embeddable objects or map entry results is undefined.

Standalone identification variables in the SELECT clause may optionally be qualified by the OBJECT operator. The SELECT clause must not use the OBJECT operator to qualify path expressions.

A result_variable may be used to name a select_item in the query result. For example,

SELECT c, COUNT(l) AS itemCount
FROM Customer c JOIN c.Orders o JOIN o.lineItems l
WHERE c.address.state = ‘CA’
ORDER BY itemCount