JPQL Left Outer Join IN One to Many mapping
By email@example.com on Jan 27, 2011
Retrieve the employees based on the below criteria where in Departments and Employees entities are associated by One to Many mapping.
"DepartmentId value is NULL or passed in departmentId as parameter".
JPA by default does a inner join in between entities .The '.' notation to mean an inner join unless it is explicietly stated to use an outer join
select o from Employee o where o.departments.departmentId is null or o.departments.departmentId=:departmentId
But the above jpql translates to below Sql
select * from Employees t0,Departmemts t1 where (to.depratmentID is null) or (to.departmentId = :1 )) and (to.departmentId = t1.departmentId))
To achieve the above query criteria use left outer join as given below
The above Left outer JPQL will rsult in below SQL.
Select * from Employees t0 where (((t0.departmentId IS NULL) OR (t0.departmentId = :1))