HQL right outer join

Question

I am trying to perform right outer join in HQL. Query creation is done as mentioned below:

Query query = this.sessionFactory
            .getCurrentSession()
            .createQuery(
"select 
       O.customer.id as id, 
       O.customer.firstName as firstName, 
       O.customer.lastName as lastName, 
       O.customer.address as address, 
       O.customer.city as city, 
       count(O.id) as totalOrders 
 from 
       Order O 
       right outer join O.customer 
 group by 
       O.customer.id");

SQL query on mysql is working fine, but the HQL query is returning the result for inner join.

SQL query is:

select c.id,
    c.firstname,
    c.lastname,
    c.city,
    count(o.id) as total_order
  from orders o right outer join customers c
  on c.id = o.customer_id group by id

Answer 1

The problem is with the way you've written your query. Because you use O.customer.XXXX, Hibernate adds an inner join between Order and Customer to the query in order to resolve O.customer. You need to re-write your query to use the results of the right inner join by introducing an alias for O.customer in the right inner join.

select C.id as id, C.firstName as firstName, C.lastName as lastName, 
  C.address as address, C.city as city, count(O.id) as totalOrders 
from Order O right outer join O.customer C 
group by C.id

If you were to look at the SQL that hibernate generated from your query, you would see that it is performing both an inner join and a right inner join between Order and Customer.