HQL and Nested Queries

It is often the case that we need to execute a query based on the results of another query. One way would be to execute the first query, get the results, parse and use them for the second query. The concern here is that since we are not concerned with results of the first query why should we have the application handle that logic ? Instead why not have the database process the two queries together and return to the application only the results of the second query. The results that the application is actually interested in. This can be achieved using inner queries or sub queries.
Considered the below HQL query:

public static void testUnRelated() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Entity e where e.id > " + 
                 "(select max(m.id) from Master m)");
    List<Entity> entities = q.list(); 
    for (Entity entity : entities) {
        System.out.println(entity.getId() + " " + entity.getName());
    }
}

The output is as below:

    select
        entity0_.ID as ID0_,
        entity0_.NAME as NAME0_,
        entity0_.DATE as DATE0_,
        entity0_.MASTER_ID as MASTER4_0_ 
    from
        ENTITY entity0_ 
    where
        entity0_.ID>(
            select
                max(master1_.ID) 
            from
                ENTITY_MASTER master1_
        )
3 entity2
4 entity100
5 entity102

As can be seen the entities with id greater than the max id in Master table were the only ones retrieved. In this case there was no connection between the two queries. Consider the case where we need Master who have entities with atleast one child entity.

public static void testRelated() {
    final Session session = sessionFactory.openSession();
    Query q = session.createQuery("from Master m where m.id IN (" +
            "select e.master.id from Entity e where e.children is not empty)");
    List<Master> masterWithGrandChildren = q.list(); 
    for (Master master : masterWithGrandChildren) {
        System.out.println(master.getId() + " " + master.getData());
    }
}

In this case the inner HQL is expected to return more than one row. Hence we have used the IN quantifier. The output is :

    select
        master0_.ID as ID1_,
        master0_.DATA as DATA1_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID in (
            select
                entity1_.MASTER_ID 
            from
                ENTITY entity1_ 
            where
                exists (
                    select
                        children2_.ID 
                    from
                        CHILD_ENTITY children2_ 
                    where
                        entity1_.ID=children2_.ENTITY_ID
                )
            )
1 master No 1
2 master No 2

The above HQL actually evaluated into a multilevel nested SQL clause. Every query produced data that was relevant for its outer query.
Other quantifiers like IN are ALL and ANY.
For ALL the HQL could be something like:

Query q = session.createQuery("from Master m where m.id <ALL (" +
    "elect e.master.id from Entity e where e.name like 'entity102')");

The SQL generated would be :

select
    master0_.ID as ID1_,
    master0_.DATA as DATA1_ 
from
    ENTITY_MASTER master0_ 
where
    master0_.ID<all (
         select
            entity1_.MASTER_ID 
         from
            ENTITY entity1_ 
         where
            entity1_.NAME like 'entity102'
   )

Similarly for ANY:

Query q = session.createQuery("from Master m where m.id = ANY (" +
              "select e.id from Entity e )");

The generated sql is:

select
    master0_.ID as ID1_,
    master0_.DATA as DATA1_ 
from
    ENTITY_MASTER master0_ 
where
    master0_.ID=any (
        select
            entity1_.ID 
        from
            ENTITY entity1_
    )