FROM clause (JPQL / Criteria API)

The FROM clause declares query identification variables for iterating over objects in the database. A query identification variable is similar to a variable in a Java enhanced for loop because both are used to iterate over objects.

This page covers the following topics:

Range variables [INNER] JOIN LEFT [OUTER] JOIN [LEFT [OUTER] | INNER] JOIN FETCH Reserved identifiers FROM and JOIN in criteria queries

Range variables

Range variables are query identification variables that iterate over all the database objects of a specific entity class hierarchy, which includes an entity class and all its descendant entity classes. Identification variables are always polymorphic (you can use type expression to exclude descendant classes from iteration).

For example, in the following query, c iterates over all the Country objects in the database:

SELECT c FROM Country AS c

The AS keyword is optional, and the same query can also be written as follows:

SELECT c FROM Country c

By default, the name of an entity class in a JPQL query is the unqualified name of the class (for example, Country with no package name). The default name can be overridden by specifying another name explicitly in the name element of the @Entityjakarta.persistence.EntityDeclares that the annotated class is an entity. annotation.

Multiple range variables are allowed. For example, the following query returns all pairs of countries that share a common border:

SELECT c1, c2 FROM Country c1, Country c2
WHERE c2 MEMBER OF c1.neighbors

Multiple variables are equivalent to nested loops in a program. The FROM clause above defines two loops. The outer loop iterate over all the Country objects, using c1. The inner loop also iterate over all the Country objects, using c2. A similar query with no WHERE clause would return all possible combinations of two countries. The WHERE clause filters any pair of countries that do not share a border, returning only neighboring countries.

Caution is required when using multiple range variables. Iteration over about 1,000,000 database objects with a single range variable might be acceptable. However, iteration over the same objects with two range variables forming nested loops (outer and inner) might prevent query execution within a reasonable response time.

Database management systems (DBMSs), including ObjectDB, try to optimize the execution of multivariable queries. Whenever possible, full nested iteration over the entire Cartesian product is avoided. For example, the query above can be executed as follows: An outer loop uses c1 to iterate over all Country objects, and an inner loop uses c2 to iterate only over the neighbors collection for the current c1. In this case, propagating the WHERE constraint to the FROM phase avoids a full iteration over the Cartesian product.

[INNER] JOIN

As discussed above, range variables represent iteration over all the database objects of a specified entity type. JPQL provides an additional type of identification variable, a join variable, which represents a more limited iteration over specified collections of objects.

The following query uses one range variable and one join variable:

SELECT c1, c2 FROM Country c1 INNER JOIN c1.neighbors c2

In JPQL, JOIN can appear only in a FROM clause. The INNER keyword is optional (that is, INNER JOIN is equivalent to JOIN). c1 is declared as a range variable that iterates over all the Country objects in the database. c2 is declared as a join variable that is bound to the c1.neighbors path and iterates only over objects in that collection.

You might have noticed that this query is equivalent to the previous neighbor query, which has two range variables and a WHERE clause. However, this second form of the query, which uses a join variable, is preferred. Besides being shorter and cleaner, the second query describes a more direct and efficient way to execute the query (using a full-range outer loop and a collection-limited inner loop) without relying on DBMS optimizations.

It is common for JPQL queries to have a single range variable that serves as a root and additional join variables that are bound to path expressions. Join variables can also be bound to path expressions that are based on other join variables that appear earlier in the FROM clause.

Join variables can also be bound to a single-value path expression. For example:

SELECT c, p.name FROM Country c JOIN c.capital p

In this case, the inner loop iterates over a single object because every Country c has only one Capital p. Join variables that are bound to a single-value expression are less commonly used because they can usually be replaced by a simpler, long path expression (which is not an option for a collection). For example:

SELECT c, c.capital.name FROM Country c

One exception is when OUTER JOIN is required because path expressions function as implicit INNER JOIN variables.

LEFT [OUTER] JOIN

To understand the purpose of OUTER JOIN, consider the following INNER JOIN query that retrieves pairs of country names and capital names:

SELECT c.name, p.name FROM Country c JOIN c.capital p

The FROM clause defines iteration over (country, capital) pairs. A country with no capital city (for example, Nauru, which does not have an official capital) is not part of any iterated pair and is therefore excluded from the query results. INNER JOIN simply skips any outer variable value (for example, a Country) that has no matching inner variable (for example, a Capital).

The behavior of OUTER JOIN is different, as demonstrated by the following query variant:

SELECT c, p.name FROM Country c LEFT OUTER JOIN c.capital p

The OUTER keyword is optional (LEFT OUTER JOIN is equivalent to LEFT JOIN). When you use OUTER JOIN, if a specific outer variable does not have any matching inner value, it is paired with a NULL value in the FROM iteration. Therefore, a Country c with no Capital city is represented as the pair (c, NULL) in the FROM iteration.

For example, unlike the INNER JOIN variant of this query that skips Nauru completely, the OUTER JOIN variant returns Nauru with a NULL value as its capital.

[LEFT [OUTER] | INNER] JOIN FETCH

JPA support for transparent navigation and fetching is convenient because it provides the illusion that all database objects are available in memory for navigation. However, this feature can also cause performance problems.

For example, consider the following query execution and result iteration:

TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Country> query =
    em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of   TypedQuery<X>   for executing a Jakarta Persistence query language statement.("SELECT c FROM Country c", Country.class);
List<Country> results = query.getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed   List<X>  .();
for (Country c : results) {
    System.out.println(c.getName() + " => " + c.getCapital().getName());
}

The query returns only Country instances. Consequently, the loop that iterates over the results is inefficient because the referenced Capital objects are retrieved one at a time; that is, the number of round trips to the database is much larger than necessary.

A simple solution is to use the following query, which returns exactly the same result objects (Country instances):

SELECT c FROM Country c JOIN FETCH c.capital

The JOIN FETCH expression is not a regular JOIN, and it does not define a join variable. Its only purpose is to specify related objects that should be fetched from the database with the query results in the same round trip. Using this query improves the efficiency of iteration over the result Country objects because it eliminates the need to retrieve the associated Capital objects separately.

Note that if the Country and Capital objects are needed only for their names, the following report query can be even more efficient:

SELECT c.name, c.capital.name FROM Country c

Reserved identifiers

The name of a JPQL query variable must be a valid Java identifier but cannot be one of the following reserved words:

ABS, ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BIT_LENGTH, BOTH, BY, CASE, CHAR_LENGTH, CHARACTER_LENGTH, CLASS, COALESCE, CONCAT, COUNT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,DELETE, DESC, DISTINCT, ELSE, EMPTY, END, ENTRY, ESCAPE, EXISTS, FALSE, FETCH,FROM, GROUP, HAVING, IN, INDEX, INNER, IS, JOIN, KEY, LEADING, LEFT, LENGTH, LIKE,LOCATE, LOWER, MAX, MEMBER, MIN, MOD, NEW, NOT, NULL, NULLIF, OBJECT, OF, OR, ORDER, OUTER, POSITION, SELECT, SET, SIZE, SOME, SQRT, SUBSTRING, SUM, THEN,TRAILING, TRIM, TRUE, TYPE, UNKNOWN, UPDATE, UPPER, VALUE, WHEN, WHERE.

JPQL variables, as well as all the reserved identifiers in the list above, are case-insensitive. Therefore, ABS, abs, Abs, and aBs are all invalid variable names.

FROM and JOIN in criteria queries

FROM query identification variables are represented in criteria queries by subinterfaces of Fromjakarta.persistence.criteria.FromRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause.:

Range variables are represented by the Rootjakarta.persistence.criteria.RootA root type in the from clause. interface.
Join variables are represented by the Joinjakarta.persistence.criteria.JoinA join to an entity, embeddable, or basic type. interface (and its subinterfaces).

Criteria query roots

The CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries.'s fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots. method serves as a factory for Rootjakarta.persistence.criteria.RootA root type in the from clause. instances.

For example, the following JPQL query, which defines two uncorrelated range variables, c1 and c2:

SELECT c1, c2 FROM Country c1, Country c2

can be built as a criteria query by using the following code:

CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c1 = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c2 = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
q.multiselectjakarta.persistence.criteria.CriteriaQuery.multiselect(Selection...)Specify the selection items that are to be returned in the query result.(c1, c2);

Unlike other CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries. methods, invoking the fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots. method does not override a previous invocation. Each time the fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots. method is invoked, a new variable is added to the query.

Criteria query joins

JOIN variables are represented in criteria queries by the Joinjakarta.persistence.criteria.JoinA join to an entity, embeddable, or basic type. interface (and its subinterfaces).

For example, the following JPQL query:

SELECT c, p.name FROM Country c LEFT OUTER JOIN c.capital p

can be built as a criteria query by using the following code:

CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
Joinjakarta.persistence.criteria.JoinA join to an entity, embeddable, or basic type.<Country> p = c.joinjakarta.persistence.criteria.From.join(String,JoinType)Create a join to the specified attribute using the given join type.("capital", JoinTypejakarta.persistence.criteria.JoinTypeDefines the three varieties of join..LEFTjakarta.persistence.criteria.JoinType.LEFTLeft outer join.);
q.multiselectjakarta.persistence.criteria.CriteriaQuery.multiselect(Selection...)Specify the selection items that are to be returned in the query result.(c, p.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"));

The Fromjakarta.persistence.criteria.FromRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause. interface provides various forms of the join method. Each invocation of join adds a new JOIN variable to the query. Because Fromjakarta.persistence.criteria.FromRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause. is the superinterface of both Rootjakarta.persistence.criteria.RootA root type in the from clause. and Joinjakarta.persistence.criteria.JoinA join to an entity, embeddable, or basic type., join methods can be invoked on Root instances (as demonstrated above) and on previously built Join instances.

Criteria query fetch joins

The following JPQL query:

SELECT c FROM Country c JOIN FETCH c.capital

can be built as a criteria query by using the following code:

CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
Fetchjakarta.persistence.criteria.FetchRepresents a join-fetched association or attribute.<Country,Capital> p = c.fetchjakarta.persistence.criteria.FetchParent.fetch(String)Create a fetch join to the specified attribute using an inner join.("capital");
q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(c);

Several forms of the fetch method are defined in the Pathjakarta.persistence.criteria.PathRepresents a simple or compound attribute path from a bound type or collection, and is a "primitive" expression. interface, which represents path expressions and is also the superinterface of the Rootjakarta.persistence.criteria.RootA root type in the from clause. and Joinjakarta.persistence.criteria.JoinA join to an entity, embeddable, or basic type. interfaces.

< JPQL SELECT ^ Query Structure JPQL WHERE >