CODEWITHSUNDEEP
springhibernatejpajpql
HibernateIndexIssues
HibernateIndexIssues

Reputation: 151

JPQL returning no results when one of the fields is null

I have a query which looks like this: @Query(value = "SELECT e FROM Entity e") which returns all of the entities (currently 15). I want to only select certain fields to make the query far more memory efficient (has lots of embedded entities I don't need).

I have 2 many to one relationships, a and b, of which one is normally null and the other has a value. If I do @Query(value = "SELECT e.a FROM Entity e") I get the 10 results where a is not null, and when I do @Query(value = "SELECT e.b FROM Entity e") I get the other 5 where b is not null. However if I do @Query(value = "SELECT e.a, e.b FROM Entity e") I get 0 results back. I would assume it would all 15, returning it as a List<Object[]>. Why does this happen, and can I change it so @Query(value = "SELECT e.a, e.b FROM Entity e") returns all of the entities?

Upvotes: 0

Views: 253

Answers (1)

Oleksii Valuiskyi
Oleksii Valuiskyi

Reputation: 2851

If e.a and e.b are entities you need left join

select a, b from Entity e left join e.a a left join e.b b

Upvotes: 2

Related Questions