Using outer join in HQL to search for entities in Hibernate

Hibernate uses cross join when generating the SQL query from HQL for entity with associations. This could cause problem when searching for entities when the association is optional. For example, let’s consider following 2 entities:


public class Parent {

@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String prop;

private Child child;


public class Child implements Serializable {

private static final long serialVersionUID = 1L;

@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String childProp;

Note the use of Hibernate @NotFound annotation for the Parent->Child association to indicate that the association is optional. If you want to search for Parent entities using its Child property, be aware that you won’t get any Parent entity that has no child association even if the search criteria is part of a OR statement. For example, let say the Parent table has the following data:

id prop childFK
1 john 1 (NULL)
2 sam (NULL)
3 john 2 1

and Child table has the data

id childProp
1 green
2 red
3 blue

The following HQL will return only Parent “john 2”, not “john 1”:

select from Parent where prop like ‘%john%’ OR chid.childProp like  ‘%green%’

This is because Hibernate generates the SQL query using cross join:

select as id2_, parent0_.childFK as childFK2_, parent0_.prop as prop2_ from Parent parent0_ cross join Child child1_ where and (parent0_.prop like ‘%joh%’ or child1_.childProp like ‘%green%’)

Note the highlighted part of the WHERE statement which filters out any Parent with no Child. To get the Parent “john 1” in the result, the following HQL should be used:

select from Parent as p left outer join p.child  as c where p.prop like ‘%john%’ OR c.childProp like ‘%green%’

The corresponding SQL query generated by Hibernate is:

 select as id2_0_, as id0_1_, parent0_.childFK as childFK2_0_, parent0_.prop as prop2_0_, child1_.childProp as childProp0_1_ from Parent parent0_ left outer join Child child1_ on where parent0_.prop like ‘%joh%’ or child1_.childProp like ‘%green%’

So it would seem Hibernate ignores the @NotFound annotation when generating the SQL from HQL and if you intend to include entities with null association in the search results, you have to use left join in your HQL statements.

About Raymond Lee
Professional Java/EE Developer, software development technology enthusiast.

Comments are closed.