Joins in Hibernate Query Language

Inner Join

Earlier we saw that entities can be joined using implicit association joining. Now we join the same two entities explicitly, using the join keyword.

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.

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).

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

This SQL also uses an inner join:

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.

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();

This generates a left outer join in the 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:

Inner Join

CONT_NAME	CTRY_NAME
Africa	Gabon
Europe	Germany
Oceania	Australia
Oceania	New Zealand

Outer Join

CONT_NAME	CTRY_NAME
Africa	Gabon
Antarctica	[none]
Asia	[none]
Europe	Germany
North America	[none]
Oceania	Australia
Oceania	New Zealand
South America	[none]

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:

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

The from and where clauses are almost identical in the generated 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.

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

The uniqueResult() method generates SQL which includes a join to the COUNTRY table.

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 has been returned.

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

 
europe.getCountries().size()

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

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.

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

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

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.

quick start