I will show visually the problem, using the great example from James answer and adding the alternative solution.
When you do the follow query, without the FETCH
:
Select e from Employee e
join e.phones p
where p.areaCode = '613'
You will have the follow results from Employee
as you expected:
EmployeeId | EmployeeName | PhoneId | PhoneAreaCode |
---|---|---|---|
1 | James | 5 | 613 |
1 | James | 6 | 416 |
But when you add the FETCH
word on JOIN
, this is what happens:
EmployeeId | EmployeeName | PhoneId | PhoneAreaCode |
---|---|---|---|
1 | James | 5 | 613 |
The generated SQL is the same for the two queries, but the Hibernate removes on memory the 416
register when you use WHERE
on the FETCH
join.
So, to bring all phones and apply the WHERE
correctly, you need to have two JOIN
s: one for the WHERE
and another for the FETCH
. Like:
Select e from Employee e
join e.phones p
join fetch e.phones //no alias, to not commit the mistake
where p.areaCode = '613'