Querying and Outer Joins

In the previous post we saw HQL 's support for inner joins. With inner joins, a rows is returned when there is at least one row from both tables that matches the join condition. However it may be needed that rows are returned even when there are no matches through the join criteria.
For this we have outer joins. From the msdn library

Outer joins, however, return all rows from at least one of the tables or views 

mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING 

search conditions. 
All rows are retrieved from the left table referenced with a left 
outer join, and all rows from the right table referenced in a right 
outer join. All rows from both tables are returned in a full outer join.

HQL support outer joins too.
I decided to execute a simple left join and right join on our entity- master relation.

public static void testOuterJoinSimple() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e left join e.master");
    List<Object[]> objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
    q = session.createQuery("from Entity e right join e.master");
    objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
}

The output and SQL is as below:

Hibernate: 
    /* 
from
    Entity e 
left join
    e.master */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    left outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID
[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : 2010-07-04 16:16:43.0
 )] and [Master] : ( id 1 , data : master No 1 )]
[Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : 2012-10-10 12:20:20.
0 )] and [Master] : ( id 1 , data : master No 1 )]
[Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : 2011-10-10 15:20:20.
0 )] and [Master] : ( id 1 , data : master No 1 )]
Hibernate: 
    /* 
from
    Entity e 
right join
    e.master */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    right outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID
[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : 2010-07-04 16:16:43.0
 )] and [Master] : ( id 1 , data : master No 1 )]
[Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : 2012-10-10 12:20:20.
0 )] and [Master] : ( id 1 , data : master No 1 )]
[Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : 2011-10-10 15:20:20.
0 )] and [Master] : ( id 1 , data : master No 1 )]
null and [Master] : ( id 2 , data : Master 2 )]
null and [Master] : ( id 3 , data : Master 3 )]

The above code leads to the following conclusions:

1. In both queries the ON clause was not specified in the HQL. Hibernate added that from the object association (The join would not work without the ON condition).
2. In case of left join, we can see that all rows in the entity table(left table) matched the condition of the query. As a result there is no record of entity with a null pair of Entity_Master
3. In case of right join only Entity_Master record with id 1 was able to meet the condition (matching with 3 rows of Entity - therefore resulting in 3 combinations). As Entity_Master records with ids 2 and 3 couldn't find a match they turned up with null pairings.

Complex Outer join conditions
It is often going to be these case where simply the association is not enough to perform the join clause. We want some additional conditions to be specified in the join condition. For this Hibernate provides the with clause

public static void testOuterJoinWith() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e left join e.master m with m.id = 2");
    List<Object[]> objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
    q = session.createQuery("from Entity e right join e.master m with m.id = 2");
    objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
}

The queries in the above code have only one modification. The join condition also specifies that Master objects must have id with a value of 2. The query now generated includes the condition

Hibernate: 
    /* 
from
    Entity e 
left join
    e.master m with m.id = 2 */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    left outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID 
            and (
                master1_.ID=2
            )
[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : 2010-07-04 16:16:43.0
 )] and null
[Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : 2012-10-10 12:20:20.
0 )] and null
[Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : 2011-10-10 15:20:20.
0 )] and null
Hibernate: 
    /* 
from
    Entity e 
right join
    e.master m with m.id = 2 */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    right outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID 
            and (
                master1_.ID=2
            )
null and [Master] : ( id 1 , data : master No 1 )]
null and [Master] : ( id 2 , data : Master 2 )]
null and [Master] : ( id 3 , data : Master 3 )]

As can be seen

1. For the left join, there were no Entity-Master pairs with a master.id of value 2. hence only Entity-null pairs where produced in the result.
2. Similarly for master as there was no match pair available, the right join resulted in every record of master occurring in the result with a null entity pair. 
3. The with keyword is also available with inner joins.

 Is the below code same as the  above HQL ??

public static void testOuterJoinWhere() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e left join e.master m where m.id = 2");
    List<Object[]> objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
    q = session.createQuery("from Entity e right join e.master m where m.id = 2");
    objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
}

The result of the above code is:

Hibernate: 
    /* 
from
    Entity e 
left join
    e.master m 
where
    m.id = 2 */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    left outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID 
    where
        master1_.ID=2
Hibernate: 
    /* 
from
    Entity e 
right join
    e.master m 
where
    m.id = 2 */ 
    select
        entity0_.ID as ID0_0_,
        master1_.ID as ID1_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        master1_.DATA as DATA1_1_ 
    from
        ENTITY entity0_ 
    right outer join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID 
    where
        master1_.ID=2
null and [Master] : ( id 2 , data : Master 2 )]

As can be seen the queries generated has a very important difference.

1. The where clause applies after the join and not as a part of the join.
2. In the first case, the left join resulted in a result set where all the entity records occurred at least once. However none of the rows in the result set had a Master.id with value 2. Hence an empty result set.
3. In the second case the right join resulted in all records of Master appearing in the join result at least once. Of these only one record satisfied the where clause and hence a 1 sized  result set.