FROM clause (JPQL / Criteria API)

The FROM clause declares query identification variables that represent iteration over objects in the database. A query identification variable is similar to a variable of a Java enhanced for loop in a program, since both are used for iteration over objects.

Range Variables

Range variables are query identification variables that iterate over all the database objects of a specific entity class hierarchy (i.e. an entity class and all its descendant entity classes). Identification variables are always polymorphic. JPQL does not provide a way to exclude descendant classes from iteration at the FROM clause level. JPA 2, however, adds support for filtering instances of specific types at the WHERE clause level by using a type expression.

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 (e.g. just Country with no package name). The default name can be overridden by specifying another name explicitly in the @Entityjavax.persistence.EntityJPA annotationSpecifies that the class is an entity.See JavaDoc Reference Page...'s nameEntity.nameannotation element(Optional) The entity name.See JavaDoc Reference Page... annotation element.

Multiple range variables are allowed. For example, the following query returns all the 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 uses c1 to iterate over all the Country objects. The inner loop uses c2 to also iterate over all the Country objects. A similar query with no WHERE clause would return all the possible combinations of two countries. The WHERE clause filters any pair of countries that do not share a border, returning as results 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. But 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 (DBMS), including ObjectDB, try to optimize the execution of multi-variable queries. Whenever possible, full nested iteration over the entire Cartesian product is avoided. The above query, for example, can be executed as follows. An outer loop iterates with c1 over all the Country objects in the database. An inner loop iterates with c2 only over the neighbors collection of the outer c1. In this case, by propagation of a WHERE constraint to the FROM phase, a full iteration over the Cartesian product is avoided.

[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 represent 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 only appear in a FROM clause. The INNER keyword is optional (i.e. 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 neighbors query, which has two range variables and a WHERE clause. However, this second query form that uses a join variable is preferred. Besides being shorter and cleaner, the second query describes the right and efficient way for executing the query (which is using a full range outer loop and a collection limited inner loop) without relying on DBMS optimizations.

It is quite 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 usually they can 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 name, capital name):

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 (e.g. 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 (e.g. any Country) that has no matching inner variable (e.g. 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 using OUTER JOIN, if a specific outer variable does not have any matching inner value it gets at least a NULL value as a matching value in the FROM iteration. Therefore, a Country c with no Capital city has a minimum representation of (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 of transparent navigation and fetch makes it very easy to use, since it provides the illusion that all the database objects are available in memory for navigation. But this feature could also cause performance problems.

For example, let's look at the following query execution and result iteration:

  TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query =
      em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();
  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, since the referenced Capital objects are retrieved one at a time, i.e. 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 specifying related objects that should be fetched from the database with the query results on the same round trip. Using this query improves the efficiency of iteration over the result Country objects because it eliminates the need for retrieving the associated Capital objects separately.

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

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

Reserved Identifiers

The name of a JPQL query variable must 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 sub interfaces of Fromjavax.persistence.criteria.FromJPA interfaceRepresents 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.See JavaDoc Reference Page...:

• Range variables are represented by the Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page... interface.
• Join variables are represented by the Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.See JavaDoc Reference Page... interface (and its sub interfaces).

Criteria Query Roots

The CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.See JavaDoc Reference Page...'s fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.See JavaDoc Reference Page... method serves as a factory of Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page... instances.

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

SELECT c1, c2 FROM Country c1, Country c2

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

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c1 = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c2 = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  q.multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the 
 query result.See JavaDoc Reference Page...(c1, c2);

Unlike other CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.See JavaDoc Reference Page... methods - invocation of the fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.See JavaDoc Reference Page... method does not override a previous invocation of that method. Every time the fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.See JavaDoc Reference Page... method is invoked - a new variable is added to the query.

Criteria Query Joins

JOIN variables are represented in criteria queries by the Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.See JavaDoc Reference Page... interface (and its sub interfaces).

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 using the following code:

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.See JavaDoc Reference Page...<Country> p = c.joinjoin(attributeName, jt)From's methodCreate a join to the specified attribute using the given
  join type.See JavaDoc Reference Page...("capital", JoinTypejavax.persistence.criteria.JoinTypeJPA enumDefines the three types of joins.See JavaDoc Reference Page....LEFTJoinType.LEFTenum constantLeft outer join.See JavaDoc Reference Page...);
  q.multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the 
 query result.See JavaDoc Reference Page...(c, p.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"));

The Fromjavax.persistence.criteria.FromJPA interfaceRepresents 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.See JavaDoc Reference Page... interface provides various forms of the join method. Every invocation of join adds a new JOIN variable to the query. Since Fromjavax.persistence.criteria.FromJPA interfaceRepresents 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.See JavaDoc Reference Page... is the super interface of both Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page... and Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.See JavaDoc Reference Page... - join methods can be invoked on Root instances (as demonstrated above) as well as on previously built Join instances.

Criteria Query Fetch Joins

SELECT c FROM Country c JOIN FETCH c.capital

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
javax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.See JavaDoc Reference Page...  Fetchjavax.persistence.criteria.FetchJPA interfaceRepresents a join-fetched association or attribute.See JavaDoc Reference Page...<Country,Capital> p = c.fetchfetch(attributeName)FetchParent's methodCreate a fetch join to the specified attribute using an 
  inner join.See JavaDoc Reference Page...("capital");
  q.selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page...(c);