Distinct Queries in HQL

Last updated: January 8, 2024

Written by: Emanuel Trandafir

Refactor Java code safely — and automatically — with OpenRewrite.

Refactoring big codebases by hand is slow, risky, and easy to put off. That’s where OpenRewrite comes in. The open-source framework for large-scale, automated code transformations helps teams modernize safely and consistently.

Each month, the creators and maintainers of OpenRewrite at Moderne run live, hands-on training sessions — one for newcomers and one for experienced users. You’ll see how recipes work, how to apply them across projects, and how to modernize code with confidence.

Join the next session, bring your questions, and learn how to automate the kind of work that usually eats your sprint time.

1. Overview

In this article, we’ll discuss distinct HQL queries and how to avoid adding the distinct keyword in SQL queries when it’s not necessary.

2. Understanding the Problem

First, let’s look at our data model and identify what we’re trying to accomplish.

We’ll use the Post and Comment entity objects, which share a one-to-many relationship. We want to retrieve a list of posts with all their associated comments.

Let’s start by trying out the following HQL query:

String hql = "SELECT p FROM Post p LEFT JOIN FETCH p.comments";
List<Post> posts = session.createQuery(hql, Post.class).getResultList();

This will generate the following SQL query:

select 
     p1_0.id,c1_0.Post_id,c1_1.id,c1_1.text,p1_0.title 
from
     Post p1_0 
left join (Post_Comment c1_0 join Comment c1_1 on c1_1.id=c1_0.comments_id) on p1_0.id=c1_0.Post_id

The result will contain duplicates. A Post will be shown as many times as there are associated Comments – a Post with three Comments will show up in the result list three times.

3. Using distinct in the HQL Query

We’ll need to use the distinct keyword in our HQL query to eliminate the duplicates:

String hql = "SELECT DISTINCT p FROM Post p LEFT JOIN FETCH p.comments";
List<Post> posts = session.createQuery(hql, Post.class).getResultList();

Now, we’re getting the correct result: There are no more duplicated Post objects. Let’s take a look at the SQL statement generated by Hibernate:

select 
     distinct p1_0.id,c1_0.Post_id,c1_1.id,c1_1.text,p1_0.title 
from 
     Post p1_0 
left join 
     (Post_Comment c1_0 join Comment c1_1 on c1_1.id=c1_0.comments_id) on p1_0.id=c1_0.Post_id

We can notice that the distinct keyword was not only used by Hibernate but also included in the SQL query. We should avoid this because it’s unnecessary and will cause performance issues.

4. Using QueryHint for distinct Keyword

Starting with Hibernate 6, distinct is always passed to the SQL query and the flag QueryHints#HINT_PASS_DISTINCT_THROUGH has been removed.

5. Conclusion

In this article, we’ve discovered that the presence of the distinct keyword in the SQL query can be unnecessary and has performance implications. After that, we learned how to use the PASS_DISTINCT_THROUGH query hint to avoid this behaviour.

The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.