Joins in Hibernate Query Language

Discover the power of explicit inner, outer and theta-style joins in Hibernate Query Language.

Associations

First we'll look at an entity association in a query.

ImplicitJoin.java
List<Country> africanCountries = session.createQuery(
        "from Country where continent.name = 'Africa'")
        .list();

The HQL query in this example uses implicit association joining (i.e. the join is not specified in full) which is available for many-to-one and one-to-one associations. This syntax is convenient but can be confusing if many associations are accessed in the query, as the underlying joins are not obvious. The preferred method for more complex queries is to use explicit join syntax. We will take a look at this next.

SQL

For this query, the CONTINENT and COUNTRY tables are joined like this:

implicit-join.sql
select
  COUNTRY.CTRY_ID,
  COUNTRY.AREA,
  COUNTRY.CONT_ID,
  COUNTRY.CURRENCY,
  COUNTRY.CTRY_NAME,
  COUNTRY.POP,
  COUNTRY.POP_UPD_ON
from
  COUNTRY,
  CONTINENT
where
  COUNTRY.CONT_ID=CONTINENT.CONT_ID
and CONT_NAME='Africa'

Inner Join

Now we join the same two entities explicitly, using the join keyword.

InnerJoin.java
List<Object[]> africanContinents = session.createQuery(
        "from Continent cont join cont.countries ctry " +
        "where cont.name = 'Africa'")
        .list();

Because we haven't included a select clause in our query, an Object array is returned which consists of two objects per row; a Continent object and a Country object.

The underlying tables are joined using an inner join.

inner-join.sql
select
  COUNTRY.CTRY_ID,
  COUNTRY.AREA,
  CONTINENT.CONT_ID,
  CONTINENT.CONT_NAME,
  COUNTRY.CURRENCY,
  COUNTRY.CTRY_NAME,
  COUNTRY.POP,
  COUNTRY.POP_UPD_ON
from
  CONTINENT
inner join
  COUNTRY
  on CONTINENT.CONT_ID=COUNTRY.CONT_ID
where
  CONT_NAME='Africa'

In this next example we use projection to return just the continent names, and the distinct keyword to remove any duplicates (without this a continent name would be duplicated if it contained multiple large countries).

Projection.java
List<String> continentsWithBigCountries = session.createQuery(
        "select distinct cont.name " +
        "from Continent cont join cont.countries ctry " +
        "where ctry.area > 100000")
        .list();

SQL

This SQL also uses an inner join:

projection.sql
select
  distinct CONT_NAME
from
  CONTINENT
  inner join
  COUNTRY
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID
where
  AREA>100000

Outer Join

The previous join statements used inner joins, which is the default. To perform an outer join use the left join clause.

OuterJoin.java
List<Object[]> allContinentsAndCountries = session.createQuery(
        "select cont.name, nvl(ctry.name, '[none]') " +
        "from Continent cont left join cont.countries ctry " +
        "with ctry.area > 100000 " +
        "order by cont.name")
        .list();

SQL

This generates a left outer join in the SQL.

outer-join.sql
select
  CONT_NAME,
  nvl(CTRY_NAME, '[none]')
from
  CONTINENT
  left outer join
  COUNTRY
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID
       and (
         AREA>100000
       )
order by
  CONT_NAME

We use an outer join in this example to ensure that at least one row is returned for every continent. We also include the with clause to supply an extra join condition.

An inner join would return a continent name, only if there is at least one matching country. The outer join version returns all continent names at least once, with a null country name if no matching countries are found.

Compare the output from the SQL using both join types:

We use the Oracle NVL function, which is defined in the Oracle 10g dialect, to convert any null country names to [none].

Theta-Style Joins

Theta-style joins use a more traditional join syntax by specifying a comma separated list of classes in the from clause and the join condition in the where clause.

This query returns any continents that share a name with a country:

Theta.java
List<Continent> sameNames = session.createQuery(
        "select cont " +
        "from Continent cont, Country ctry " +
        "where cont.name = ctry.name")
        .list();

SQL

The from and where clauses are almost identical in the generated SQL.

theta.sql
select
  CONTINENT.CONT_ID,
  CONTINENT.CONT_NAME
from
  CONTINENT,
  COUNTRY
where
  CONTINENT.CONT_NAME=COUNTRY.CTRY_NAME

The main reason to use theta-style syntax is to specify a join that is not mapped with an association.

Fetch

Suppose we retrieve a Continent object using HQL knowing that we will be accessing the Country objects that belong to that Continent. We join to the Country class to retrieve the data in one SQL statement.

FetchBefore.java
Continent europe = (Continent) session.createQuery(
        "select cont " +
        "from Continent cont join cont.countries " +
        "where cont.name = 'Europe'")
        .uniqueResult();

The SQL includes a join to the COUNTRY table.

fetch-before.sql
select
  CONTINENT.CONT_ID,
  CONTINENT.CONT_NAME
from
  CONTINENT
  inner join
  COUNTRY
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID
where
  CONT_NAME='Europe'

However, if we take a look at the select clause, we can see that no Country data is returned.

Now suppose we wish to see the number of countries in Europe.

FetchSize.java
europe.getCountries().size()

When we run this statement, the following SQL is triggered:

fetch-size.sql
select
  CTRY_ID,
  AREA,
  CONT_ID,
  CURRENCY,
  CTRY_NAME,
  POP,
  POP_UPD_ON
from
  COUNTRY
where
  CONT_ID=?

The join to cont.countries in the first SQL statement appears to be redundant. This is because, by default, Hibernate returns a proxy for the Country objects.

To eagerly fetch the Country objects using a single SQL statement, add the fetch keyword to the HQL query.

Fetch.java
Continent europe = (Continent) session.createQuery(
        "select cont " +
        "from Continent cont join fetch cont.countries " +
        "where cont.name = 'Europe'")

This time, the following SQL is generated, and all the objects are populated in the countries collection immediately:

fetch.sql
select
  CONTINENT.CONT_ID,
  COUNTRY.CTRY_ID ,
  CONTINENT.CONT_NAME,
  COUNTRY.AREA,
  COUNTRY.CURRENCY,
  COUNTRY.CTRY_NAME,
  COUNTRY.POP,
  COUNTRY.POP_UPD_ON
from
  CONTINENT
  inner join
  COUNTRY
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID
where
  CONT_NAME='Europe'

The getCountries().size() method now accesses the in memory list without executing any further SQL.