FROM clause

The FROM clause specifies the collections of objects to which the query is to be applied. Each collection is specified either by an abstract schema name (ASN) or by a path expression identifying a relationship. An identification variable is defined for each collection.

Conceptually, the semantics of the query is to form a temporary collection of tuples, R, with elements consisting of all possible combinations of objects from the collections. This collection is subject to the constraints imposed by any path relationships and by the JOIN operation. The JOIN can be either an inner or outer join.

The identification variables are bound to elements of the tuple. After forming the temporary collection, the search conditions of the WHERE clause are applied to R, and yield a new temporary collection, R1. The ORDER BY, GROUP BY, HAVING, and SELECT clauses are applied to R1 to yield the final result.

from_clause::=FROM identification_variable_declaration [, {identification_variable_declaration |  collection_member_declaration } ]*
 identification_variable_declaration::= range_variable_declaration [join]*
 join :=  [ { LEFT [OUTER] | INNER  }]  JOIN  {collection_valued_path_expression | single_valued_path_expression}
 [AS] identifier 

Examples: Joining collections

DeptBean contains records 10, 20, and 30. EmpBean contains records 1, 2, and 3 related to department 10, and records 4 and 5 related to department 20. Department 30 has no employees.

SELECT d FROM  DeptBean AS d, EmpBean AS e
WHERE d.name = e.name

The comma syntax performs an inner join resulting in all possible combinations. In this example, R would consist of 15 tuples (3 departments x 5 employees). If any collection is empty, then R is also empty. The keyword AS is optional.

This example shows that a collection can be joined with itself.

SELECT d FROM DeptBean AS d, DeptBean AS d1

R would consist of 9 tuples (3 departments x 3 departments).

Examples: Relationship joins

A collection can be a relationship based on a previously declared identifier as in

SELECT e FROM DeptBean AS d , IN (d.emps) AS e

R would contain 5 tuples. Department 30 would not appear in R because it contains no employees. Department 10 would appear in 3 tuples and department 20 would appear in 2 tuples. IN can only refer to multi-valued relationships. The following is not valid

SELECT m FROM EmpBean e, IN( e.dept.mgr) as m INVALID

When joining with a relationship the alternate syntax INNER JOIN ( keyword INNER is optional) can also be used, as shown here.

SELECT e FROM DeptBean AS d INNER JOIN d.emps AS e

An ASN declaration (d in the previous query) can be followed by one or more join clauses. The relationship following the JOIN keyword must be related (directly or indirectly) to the ASN declaration. Unlike the case with the IN clause, relationships used in a join clause can be single- or multi-valued. This query has the same semantics as the query

SELECT e FROM DeptBean AS d , IN (d.emps) AS e

We can use multiple joins together.

SELECT m FROM EmpBean e JOIN e.dept d JOIN d.mgr m

This is equivalent to

SELECT m FROM EmpBean e JOIN e.dept.mgr m

Examples: OUTER JOIN

An OUTER JOIN results in a temporary collection containing combinations of the left and right operands, subject to the relationship constraints and such that the left operand always appears in R. In the example an outer join results in a temporary collection R containing department 30, even though the collection d.emps is empty. The tuple contains Department 30 along with a NULL value. References to e in the query yields a null value.

SELECT e FROM DeptBean AS d LEFT OUTER JOIN d.emps AS e

The keyword OUTER is optional, as shown here.

SELECT e FROM DeptBean AS d LEFT JOIN d.emps AS e

We can also use combinations of INNER and OUTER JOIN.

SELECT m FROM EmpBean e JOIN e.dept d LEFT JOIN d.mgr m

Related concepts

EJB query language

Related tasks

Use EJB query