Search code examples
jpaspring-data-jpah2openjpa

Why LEFT OUTER JOIN is not working with @Query

I am using H2 DB and JPA. I have a below query with left outer join, but when I am printing the logs I cant see the "left outer join" part in the generated query and its not working.

@Query("select count(p) from Employee p left outer join Department d where " +           
            "p.id = d.eid"
 }

Generated Query Logs :

SELECT COUNT(p.ID) FROM EMPLOYEE p, DEPARTMENT d WHERE p.ID=d.EID

The "left outer join" is not there in the generated query and it's not working.

Anything I am missing, any suggestion would be helpful.

Solution

  • I assume you're using Spring JPA because the @Query annotation doesn't exist in JPA specification.

    You can either use the native syntax:

    @Query("select count(p) from Employee p left outer join Department d on p.id = d.eid <where other filters>", 
    nativeQuery = true)

    Or the JPQL syntax:

    @Query("select count(p) from Employee p left outer join p.department d <where other filters>")

    Supposing the name of the association is department in Employee entity.

    Worth noting that, in this query in specific, it doesn't make sense the join clause. If the idea is to count the number of employees - despite the situation of their relationship with a department, why to use the join at all? You would be better of using this query:

    @Query("select count(p) from Employee p")