X

JPQL Left Outer Join IN One to Many mapping

Guest Author

UseCase Description

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

"Select o from Employees o  LEFT OUTER JOIN
o.departments as Dept where Dept is null or Dept.departmentId=:deptNo".

 The above Left outer JPQL will rsult in below SQL.

Select * from Employees t0 where (((t0.departmentId IS
NULL) OR (t0.departmentId = :1))

Join the discussion

Comments ( 2 )
  • aidar Monday, June 20, 2011

    Pachunoori, this was handy. Thank you. Thanks google also.


  • aidar Monday, June 20, 2011

    Select * from Employees t0 where (((t0.departmentId IS NULL) OR (t0.departmentId = :1))

    seems like you have extra opening bracket after where clause.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.