An inner join may be implicitly specified by the use of a cartesian product in the FROM clause and a join condition in the WHERE clause. In the absence of a join condition, this reduces to the cartesian product.
The main use case for this generalized style of join is when a join condition does not involve a foreign key relationship that is mapped to an entity relationship.
SELECT c FROM Customer c, Employee e WHERE c.hatsize = e.shoesize
In general, use of this style of inner join (also referred to as theta-join) is less typical than explicitly defined joins over entity relationships.
Inner Joins (Relationship Joins)
The syntax for the inner join operation is:
[INNER] JOIN join_association_path_expression [AS] identification_variable
For example, the query below joins over the relationship between customers and orders. This type of join typically equates to a join over a foreign key relationship in the database:
SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1
The keyword INNER may optionally be used:
SELECT c FROM Customer c INNER JOIN c.orders o WHERE c.status = 1
This is equivalent to the following query using the earlier IN construct, defined in EJB 2.1. It selects those customers of status 1 for which at least one order exists:
SELECT OBJECT(c) FROM Customer c, IN(c.orders) o WHERE c.status = 1
Left Outer Joins
LEFT JOIN and LEFT OUTER JOIN are synonymous. They enable the retrieval of a set of entities where matching values in the join condition may be absent.
The syntax for a left outer join is:
LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
SELECT c FROM Customer c LEFT JOIN c.orders o WHERE c.status = 1
The keyword OUTER may optionally be used:
SELECT c FROM Customer c LEFT OUTER JOIN c.orders o WHERE c.status = 1
An important use case for LEFT JOIN is in enabling the prefetching of related data items as a side effect of a query. This is accomplished by specifying the LEFT JOIN as a FETCH JOIN.
A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN is specified over an entity and its related entities.
The syntax for a fetch join is:
fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression
The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.
The following query returns a set of departments. As a side effect, the associated employees for those departments are also retrieved, even though they are not part of the explicit query result. The persistent fields or properties of the employees that are eagerly fetched are fully initialized. The initialization of the relationship properties of the employees that are retrieved is determined by the metadata for the Employee entity class.
SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1
A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.
JPQL queries may specify one or more JOIN FETCH declarations, which allow the query to specify which fields in the returned instances will be PRE-FETCHED:
SELECT x FROM Magazine x JOIN FETCH x.articles WHERE x.title = 'JDJ'
The query above returns Magazine instances and GUARANTEES that the articles field will already be fetched in the returned instances.
Multiple fields may be specified in separate JOIN FETCH declarations:
SELECT x FROM Magazine x JOIN FETCH x.articles JOIN FETCH x.authors WHERE x.title = 'JDJ'
Collection Member Declarations
An identification variable declared by a collection_member_declaration ranges over values of a collection obtained by navigation using a path expression. Such a path expression represents a navigation involving the association-fields of an entity abstract schema type. Because a path expression can be based on another path expression, the navigation can use the association-fields of related entities.
An identification variable of a collection member declaration is declared using a special operator, the reserved identifier IN. The argument to the IN operator is a collection-valued path expression. The path expression evaluates to a collection type specified as a result of navigation to a collection-valued association-field of an entity abstract schema type.
The syntax for declaring a collection member identification variable is as follows:
collection_member_declaration ::= IN (collection_valued_path_expression) [AS] identification_variable
For example, the query:
SELECT DISTINCT o FROM Order o JOIN o.lineItems l JOIN l.product p WHERE p.productType = 'office_supplies'
may equivalently be expressed as follows, using the IN operator:
SELECT DISTINCT o FROM Order o, IN(o.lineItems) l WHERE l.product.productType = 'office_supplies'
In this example, lineItems is the name of an association-field whose value is a collection of instances of the abstract schema type LineItem. The identification variable l designates a member of this collection, a single LineItem abstract schema type instance. In this example, o is an identification variable of the abstract schema type Order.