Using relationships in the Java Persistence Query Language

The Java Persistence Query Language supports using relationships as defined in the object model in the query. The syntax of a relationship access expression is the same as in Java: a navigational expression using a dot as in o.customer. Please note, the query does not depend on the mapping of the relationship field to tables, columns and foreign keys in the underlying database schema. The type of the relationship field makes a difference, because a navigational expression is only allowed for single valued relationships.

Path Expressions:

A navigational expression might occur in any clause of a query. The first sample query navigates the Order<->Customer relationship in the WHERE clause selecting orders with a customer having the specified name. The other queries navigate the same relationship in the SELECT and GROUP BY clause:

     SELECT o FROM Order o WHERE o.customer.name = :name
     SELECT o.customer.name FROM Order o WHERE o.totalPrice > :limit
     SELECT AVG(o.totalPrice) FROM Order o GROUP BY o.customer

A path expression might navigate multiple single valued relationship fields. The following query navigates from an order to its customer to the related country to check a country state field:

     SELECT o FROM Order o WHERE o.customer.country.code = 'de'

JOIN Clause:

The FROM clause allows specifying an identification variable for a relationship field in a JOIN clause. The path expression in a JOIN clause always navigates a single relationship. Multiple navigations like o.customer.country are not allowed in a JOIN clause. Instead the FROM clause defines multiple JOIN clauses each of them navigating s single relationship. The following query is equivalent to the above query having a path with multiple navigations:

     SELECT o FROM Order o JOIN o.customer c JOIN c.country co 
     WHERE co.code = 'de'

Defining a JOIN variable might ease the query if the same relationship field is used multiple times. The following queries are equivalent:

     SELECT o FROM Order o 
     WHERE o.customer.name = :name AND o.customer.country = :country AND
           o.customer.city = :city

     SELECT o FROM Order o JOIN o.customer c 
     WHERE c.name = :name AND c.country = :country AND c.city = :city

A collection valued relationship field cannot be navigated in a path expression using a dot. Instead, the query declares an an identification variable to range over the elements of the collection. In the following queries the identification variable o denotes an element in the orders collection relationship of Customer, not the orders collection itself.

     SELECT c FROM Customer c JOIN c.orders o WHERE o.totalPrice > 1000
     SELECT c FROM Customer c, IN(c.orders) o WHERE o.totalPrice > 1000

Inner/Outer Joins:

A relationship navigation inside of a path expression uses inner join semantics. This means, if the relationship navigation evaluates to null this value is not used when determining the query result. In the following query an order without customer is not returned because the relationship navigation o.customer of the path expression o.customer.name evaluates to null.

     SELECT o FROM Order o WHERE o.customer.name = :name

The JOIN clause supports inner and outer joins. An inner join has the form [INNER] JOIN path [AS] variable, an outer join uses LEFT JOIN or LEFT OUTER JOIN as keywords. Although the following query does not have a WHERE clause, it restricts the result to order instance having a customer.

     SELECT o FROM Order o JOIN o.customer c

The reason is the inner join specified as part of the FROM clause. It excludes any order without customer and returns the same result as

     SELECT o FROM Order o WHERE o.customer IS NOT NULL

Here is an example for an outer join query.

     SELECT c.name FROM Order o LEFT OUTER JOIN o.customer c

It returns the name of the related customer for all orders. The query result includes null for all orders without customer. So the outer join query might return more results (the null values) as the corresponding query using an inner join in the FROM clause.

Fetch Join Clause:

The fetch join clause allows prefetching of relationships. The following query selects specific orders, but as a side effect of the query the related customers are fetched too:

     SELECT o FROM Order o JOIN FETCH o.customer 
     WHERE o.totalPrice > :limit

Collection valued relationships may also be prefetched:

     SELECT c FROM Customer c JOIN FETCH c.orders WHERE c.name = :name

Adding a fetch join clause does not change the query result. But it might improve the performance, because it allows the persistence provider to reduce the number of round trips to the database. A typical scenario is an application that executes a query, iterates the query result and then navigates a relationship field from the query result instances. This may result in multiple round trips to the database, because the relationships navigation executes its own SQL statement. A fetch join query might load the relationship fields along with the query result in a single SQL statement.

Other Expressions using Relationships:

The Java Persistence Query Language includes operators and functions that may be applied to relationship fields.

The IS NULL operator may be used to check whether a single valued relationship is null. This query returns orders not having a customer: SELECT o FROM Order o WHERE o.customer IS NULL

The IS EMPTY operator checks whether a collection has elements. This query returns customers without any order: SELECT c FROM Customer c WHERE c.orders IS EMPTY

The MEMBER OF operator checks whether a value is a member of a collection. This query returns the customer having the specified order in its orders collection: SELECT c FROM Customer c WHERE :order MEMBER OF c.orders

The SIZE function returns the number of elements in a collection. This query returns customers having at least 5 orders: SELECT c FROM Customer c WHERE SIZE(c.orders) >= 5