Quering and Inner joins

In the rich object model that Hibernate provides us, it allows us to create object associations. These associations which represent records in other tables would be a weak functionality if there was no query support for them.
These are the tables I used in my previous posts. The data set is as below:

These tables are associated by foreign keys/ relations. We correlate the data in SQL using joins, and in hibernate we navigate the object associations. Thus while Entity table has a foreign key relation with Master table, the Entity object has a Master reference within it. Thus most object graph navigation will actually result in join queries being fired on the database.
Before we enter into joins there are two object associations that do not result in SQL join queries:

1. A foreign key column access

public static void testImplictInnerJoinViaSelect() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e where e.master.id = 1");
    List<Entity> entities = q.list(); 
    System.out.println(entities);
}

The SQL query for the above association is:

from
    Entity e 
where
    e.master.id = 1 */     
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.MASTER_ID=1

Although we navigated the entity master association as the search was simply on the id (which is a foreign key in entity) there was no join.
2. The other is when we are using components.
As components represent nothing but a group of columns within the same row, there is no association with any other table and therefore no join.

Among joins, the first join we consider is the inner join.
The below select  clause will result in an inner join being created:

public static void testInnerJoinViaSelect() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("select e.master.data from Entity e where e.master.id = 1");
    List<String> datas = q.list(); 
    System.out.println(datas);
}

Hibernate: 
    /* select
        e.master.data 
    from
        Entity e 
    where
        e.master.id = 1 */ 
        select
            master1_.DATA as col_0_0_ 
        from
            ENTITY entity0_,
            ENTITY_MASTER master1_ 
        where
            entity0_.MASTER_ID=master1_.ID 
            and entity0_.MASTER_ID=1
[master No 1, master No 1, master No 1]

The result was an implicit inner join between Entity and Master tables on the Master Id column. A similar join will result from the below where condition:

public static void testImplicitInnerJoinViaWhere() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e where e.master.data = 'master No 1'");
    List<Entity> datas = q.list(); 
    System.out.println(datas);
}

Hibernate: 
    /* 
from
    Entity e 
where
    e.master.data = 'master No 1' */ 
    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_,
        ENTITY_MASTER master1_ 
    where
        entity0_.MASTER_ID=master1_.ID 
        and master1_.DATA='master No 1'
[[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : 2010-07-04 16:16:43.
0 )], [Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : 2012-10-10 12:
20:20.0 )], [Entity] : ( id 3 , data : entity3 , master.Id : 1 , date : 2011-10-
10 15:20:20.0 )]]

In this case the implicit join occurred because of the condition in where clause.
Implicit joins always occur along a one to one or a many to one association only. They are not involved on collections.
Creating an explicit Inner join:
The above query could also be written using an explicit join.

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

The output is as below:

Hibernate: 
    /* 
from
    Entity e 
join
    e.master m 
where
    m.data = 'master No 1' */ 
    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_ 
    inner join
        ENTITY_MASTER master1_ 
            on entity0_.MASTER_ID=master1_.ID 
    where
        master1_.DATA='master No 1'
[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 )]

The results of the last query and previous one also vary in the type of objects returned. While the implicit join only returned the records from the table mapped to Entity class , the explicit query considered the Entity class and its joined Master class.

Thus the data fetched (in absence of projection/select) is controlled by the details of the from clause.

If we need only the Entity records we simply need to include the select clause.
Consider the reverse one to many association. One Entity object has a set of children. If we need only entity-child pairs where child key is of the form "100_" then

public static void testExpicitInnerJoinCollection() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e join e.children c where c.key like '100_'");
    List<Object[]> objects = q.list(); 
    for (Object[] object : objects) {
        System.out.println(object[0] + " and " + object[1]);
    }
}

The resultant output is :

Hibernate: 
    /* 
from
    Entity e 
join
    e.children c 
where
    c.key like '100_' */ 
    select
        entity0_.ID as ID0_0_,
        children1_.ID as ID2_1_,
        entity0_.NAME as NAME0_0_,
        entity0_.DATE as DATE0_0_,
        entity0_.MASTER_ID as MASTER4_0_0_,
        children1_.`KEY` as KEY2_2_1_,
        children1_.ENTITY_ID as ENTITY3_2_1_ 
    from
        ENTITY entity0_ 
    inner join
        CHILD_ENTITY children1_ 
            on entity0_.ID=children1_.ENTITY_ID 
    where
        children1_.`KEY` like '100_'
[Entity] : ( id 1 , data : newOne , master.Id : 1 , date : 2010-07-04 16:16:43.0
 )] and [Child] : ( id 1 , key : 1001 , parent.Id : 1 )]
[Entity] : ( id 2 , data : entity2 , master.Id : 1 , date : 2012-10-10 12:20:20.
0 )] and [Child] : ( id 2 , key : 1001 , parent.Id : 2 )]

If the query was "Find all Entity record with children whose key starts with 100"

Query q = session.createQuery("select e from Entity e join e.children c where c.key like '100%'");
List<Entity> entities = q.list();

The query would be

select
   entity0_.ID as ID0_,
   entity0_.NAME as NAME0_,
   entity0_.DATE as DATE0_,
   entity0_.MASTER_ID as MASTER4_0_ 
from
   ENTITY entity0_ 
inner join
   CHILD_ENTITY children1_ 
   on entity0_.ID=children1_.ENTITY_ID 
where
   children1_.`KEY` like '100%'