FROM Clause

The FROM clause defines the domain of the query by declaring identification variables.

Identifiers

An identifier is a sequence of one or more characters. The first character must be a valid first character (letter, $, _) in an identifier of the Java programming language (hereafter in this chapter called simply “Java”). Each subsequent character in the sequence must be a valid non-first character (letter, digit, $, _) in a Java identifier. (For details, see the Java SE API documentation of the isJavaIdentifierStart and isJavaIdentifierPart methods of the Character class.) The question mark (?) is a reserved character in the query language and cannot be used in an identifier.

A query language identifier is case-sensitive with two exceptions:

• Keywords

• Identification variables

An identifier cannot be the same as a query language keyword. Here is a list of query language keywords:

ALL AND ANY AS ASC AVG BETWEEN BY COUNT CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DELETE DESC DISTINCT EMPTY EXISTS

FALSE FETCH FROM GROUP HAVING IN INNER IS JOIN LEFT LIKE MAX MEMBER MIN MOD NEW

NOT NULL OBJECT OF OUTER OR ORDER SELECT SOME SUM TRIM TRUE UNKNOWN UPDATE UPPER WHERE

It is not recommended that you use a SQL keyword as an identifier, because the list of keywords may expand to include other reserved SQL words in the future.

Identification Variables

An identification variable is an identifier declared in the FROM clause. Although the SELECT and WHERE clauses can reference identification variables, they cannot declare them. All identification variables must be declared in the FROM clause.

Because an identification variable is an identifier, it has the same naming conventions and restrictions as an identifier with the exception that an identification variables is case-insensitive. For example, an identification variable cannot be the same as a query language keyword. (See the preceding section for more naming rules.) Also, within a given persistence unit, an identification variable name must not match the name of any entity or abstract schema.

The FROM clause can contain multiple declarations, separated by commas. A declaration can reference another identification variable that has been previously declared (to the left). In the following FROM clause, the variable t references the previously declared variable p:

FROM Player p, IN (p.teams) AS t

Even if an identification variable is not used in the WHERE clause, its declaration can affect the results of the query. For an example, compare the next two queries. The following query returns all players, whether or not they belong to a team:

SELECT p
FROM Player p

In contrast, because the next query declares the t identification variable, it fetches all players that belong to a team:

SELECT p
FROM Player p, IN (p.teams) AS t

The following query returns the same results as the preceding query, but the WHERE clause makes it easier to read:

SELECT p
FROM Player p
WHERE p.teams IS NOT EMPTY

An identification variable always designates a reference to a single value whose type is that of the expression used in the declaration. There are two kinds of declarations: range variable and collection member.

Range Variable Declarations

To declare an identification variable as an abstract schema type, you specify a range variable declaration. In other words, an identification variable can range over the abstract schema type of an entity. In the following example, an identification variable named p represents the abstract schema named Player:

FROM Player p

A range variable declaration can include the optional AS operator:

FROM Player AS p

In most cases, to obtain objects a query uses path expressions to navigate through the relationships. But for those objects that cannot be obtained by navigation, you can use a range variable declaration to designate a starting point (or root).

If the query compares multiple values of the same abstract schema type, then the FROM clause must declare multiple identification variables for the abstract schema:

FROM Player p1, Player p2

For a sample of such a query, see Comparison Operators.

Collection Member Declarations

In a one-to-many relationship, the multiple side consists of a collection of entities. An identification variable can represent a member of this collection. To access a collection member, the path expression in the variable’s declaration navigates through the relationships in the abstract schema. (For more information on path expressions, see the following section.) Because a path expression can be based on another path expression, the navigation can traverse several relationships. See Traversing Multiple Relationships.

A collection member declaration must include the IN operator, but it can omit the optional AS operator.

In the following example, the entity represented by the abstract schema named Player has a relationship field called teams. The identification variable called t represents a single member of the teams collection.

FROM Player p, IN (p.tea
ms) t

Joins

The JOIN operator is used to traverse over relationships between entities, and is functionally similar to the IN operator.

In the following example, the query joins over the relationship between customers and orders:

SELECT c
 FROM Customer c JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000

The INNER keyword is optional:

SELECT c
 FROM Customer c INNER JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000

These examples are equivalent to the following query, which uses the IN operator:

SELECT c
 FROM Customer c, IN(c.orders) o
 WHERE c.status = 1 AND o.totalPrice > 10000

You can also join a single-valued relationship.

SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = :sport

A LEFT JOIN or LEFT OUTER JOIN retrieves a set of entities where matching values in the join condition may be absent. The OUTER keyword is optional.

SELECT c.name, o.totalPrice
FROM Order o LEFT JOIN o.customer c

A FETCH JOIN is a join operation that returns associated entities as a side-effect of running the query. In the following example, the query returns a set of departments, and as a side-effect, the associated employees of the departments, even though the employees were not explicitly retrieved by the SELECT clause.

SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1