WAS v8.5 > Develop applications > Develop Dynamic and EJB query > Develop applications that use EJB query > EJB query languageFROM 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.nameThe 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 validSELECT 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 querySELECT 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 toSELECT 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 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
Use EJB query