How a WHERE Clause Works

The following query retrieves only countries with a population size above a population sizep:

SELECTcFROMCountrycWHEREc.population >:p

The FROM clause of this query defines an iteration over all theCountry objects in the database using thec range variable. Before passing theseCountry objects to the SELECT clause for collecting as query results, the WHERE clause gets an opportunity to function as a filter. The boolean expression in the WHERE clause, which is also known as the WHERE predicate, defines which objects to accept. OnlyCountry objects for which the predicate expression evaluates toTRUE are passed to the SELECT clause and then collected as query results.

WHERE Predicate and Indexes

Formally, the WHERE clause functions as a filter between the FROM and the SELECT clauses. Practically, if a properindex is available, filtering is done earlier during FROM iteration. In the above population query, if an index is defined on thepopulation field ObjectDB can use that index to iterate directly onCountry objects that satisfy the WHERE predicate. For entity classes with millions of objects in the database there is a huge difference in query execution time if proper indexes are defined.

WHERE Filter in Multi Variable Queries

In a multi-variable query the FROM clause defines iteration on tuples. In this case the WHERE clause filters tuples before passing them to the SELECT clause.

For example, the following query retrieves all the countries with population size that exceeds a specified limit and also have an official language from a specified set of languages:

SELECTc,lFROMCountrycJOINc.languageslWHEREc.population >:pANDlin:languages

The FROM clause of this query defines iteration over (country, language) pairs. Only pairs that satisfy the WHERE clause are passed through to the SELECT.

In multi-variable queries the number of tuples for iteration might be very large even if the database is small, making indexes even more essential.

JPQL Expressions in WHERE

The above queries demonstrate only a small part of the full capabilities of a WHERE clause.
The real power of the JPQL WHERE clause is derived from the richJPQL expression syntax,
which includes many operators (arithmetic operators, relational operators, logical operators) and functions (numeric functions, string functions, collection functions). The WHERE predicate is always a boolean JPQL expression.JPQL expressions are also used in other JPQL query clauses but they are especially dominant in the WHERE clause.

WHERE in Criteria Queries

TheCriteriaQueryjakarta.persistence.criteria.CriteriaQuery - JPA Interface The {@code CriteriaQuery} interface defines functionality that is specific to top-level queries. interface provides twowhere methods for setting the WHERE clause.

Single Restriction

The firstwhereCriteriaQuery.where(restriction) - JPA Method Modify the query to restrict the query result according to the specified boolean expression. method takes oneExpressionjakarta.persistence.criteria.Expression - JPA Interface Type for query expressions.<Boolean> argument and uses it as the WHERE clause content (overriding previously set WHERE content if any).

For example, the following JPQL query:

SELECTcFROMCountrycWHEREc.population >:p

can be built by using thecriteria query API as follows:

CriteriaQueryjakarta.persistence.criteria.CriteriaQuery-JPAInterfaceThe {@codeCriteriaQuery}interface defines functionality that is specificto top-level queries.<Country> q= cb.createQueryCriteriaBuilder.createQuery(resultClass)-JPAMethodCreate aCriteriaQuery objectwith the given result type.(Country.class);Rootjakarta.persistence.criteria.Root-JPAInterfaceA root type in the from clause.<Country> c= q.fromAbstractQuery.from(entityClass)-JPAMethodCreate and add a query root correspondingto the given entity, forming a cartesian productwith any existing roots.(Country.class);  q.selectCriteriaQuery.select(selection)-JPAMethodSpecify the item that isto be returned in the query result.(c);ParameterExpressionjakarta.persistence.criteria.ParameterExpression-JPAInterfaceType of criteria query parameter expressions.<Integer> p= cb.parameterCriteriaBuilder.parameter(paramClass)-JPAMethodCreate a parameter expression.(Integer.class);  q.whereCriteriaQuery.where(restriction)-JPAMethodModify the queryto restrict the query result accordingto the specifiedboolean expression.(cb.gtCriteriaBuilder.gt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is greater than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("population"), p));

Multiple Restrictions

The secondwhere method takes a variable number of arguments ofPredicatejakarta.persistence.criteria.Predicate - JPA Interface The type of a simple or compound predicate: a conjunction or disjunction of restrictions. type and uses an AND conjunction as the WHERE clause content (overriding previously set WHERE content if any):

For example, the following JPQL query:

SELECTcFROMCountryWHEREc.population >:pANDc.area <:a

can be built as a criteria query as follows:

CriteriaQueryjakarta.persistence.criteria.CriteriaQuery-JPAInterfaceThe {@codeCriteriaQuery}interface defines functionality that is specificto top-level queries. q= cb.createQueryCriteriaBuilder.createQuery(resultClass)-JPAMethodCreate aCriteriaQuery objectwith the given result type.(Country.class);Rootjakarta.persistence.criteria.Root-JPAInterfaceA root type in the from clause.<Country> c= q.fromAbstractQuery.from(entityClass)-JPAMethodCreate and add a query root correspondingto the given entity, forming a cartesian productwith any existing roots.(Country.class);  q.selectCriteriaQuery.select(selection)-JPAMethodSpecify the item that isto be returned in the query result.(c);ParameterExpressionjakarta.persistence.criteria.ParameterExpression-JPAInterfaceType of criteria query parameter expressions.<Integer> p= cb.parameterCriteriaBuilder.parameter(paramClass)-JPAMethodCreate a parameter expression.(Integer.class);ParameterExpressionjakarta.persistence.criteria.ParameterExpression-JPAInterfaceType of criteria query parameter expressions.<Integer> a= cb.parameterCriteriaBuilder.parameter(paramClass)-JPAMethodCreate a parameter expression.(Integer.class);  q.whereCriteriaQuery.where(restrictions)-JPAMethodModify the queryto restrict the query result accordingto the conjunction of the specified restriction predicates.(      cb.gtCriteriaBuilder.gt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is greater than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("population"), p),      cb.ltCriteriaBuilder.lt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is less than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("area"), a)  );

Thewhere setting above is equivalent to explicitly building an AND conjunction, as so:

  q.whereCriteriaQuery.where(restriction)-JPAMethodModify the queryto restrict the query result accordingto the specifiedboolean expression.(      cb.andCriteriaBuilder.and(x,y)-JPAMethodCreate a conjunction of the givenboolean expressions.(          cb.gtCriteriaBuilder.gt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is greater than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("population"), p),          cb.ltCriteriaBuilder.lt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is less than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("area"), a)      )  );

The variable argument form of thewhere method always uses AND. Therefore, using OR requires building an OR expression explicitly:

  q.whereCriteriaQuery.where(restriction)-JPAMethodModify the queryto restrict the query result accordingto the specifiedboolean expression.(      cb.orCriteriaBuilder.or(x,y)-JPAMethodCreate a disjunction of the givenboolean expressions.(          cb.gtCriteriaBuilder.gt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is greater than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("population"), p),          cb.ltCriteriaBuilder.lt(x,y)-JPAMethodCreate a predicatefor testing whether the first argument is less than the second.(c.getPath.get(attributeName)-JPAMethodCreate a path correspondingto the referenced attribute.("area"), a)      )  );

See theLogical Operators page for explanations on boolean expressions and predicates that can be used in a criteria query WHERE clause.