2.10.2.3.6. JPQL 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 publishers and magazines. This type of join typically equates to a join over a foreign key relationship in the database.
SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue 1000000
The keyword INNER may optionally be used:
SELECT pub FROM Publisher pub INNER JOIN pub.magazines mag WHERE pub.revenue 1000000
This is equivalent to the following query using the earlier IN construct.
It selects those publishers with revenue of over 1 million for which at least one magazine exists:
SELECT OBJECT(pub) FROM Publisher pub, IN(pub.magazines) mag WHERE pub.revenue 1000000
The query below joins over Employee, ContactInfo and Phone. ContactInfo is an embeddable class that consists of an address and set of phones. Phone is an entity.
SELECT p.vendor
FROM Employee e JOIN e.contactInfo c JOIN c.phones p
WHERE c.address.zipcode = '95054'