The best way to use UNION, INTERSECT, and EXCEPT with Hibernate

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

You can earn a significant passive income stream from promoting my book, courses, tools, training, or coaching subscriptions.

If you're interested in supplementing your income, then join my affiliate program.

Introduction

In this article, we are going to see how we can write UNION, INTERSECT, and EXCEPT queries with Hibernate.

While you could have always used all these relational set operations using native SQL queries, starting with Hibernate 6, you can use UNION, INTERSECT, and EXCEPT in JPQL and Criteria entity queries.

This is all possible because of the new Semantic Query Model that Hibernate has introduced in version 6.

Domain Model

Let’s assume we have the following Category and Tag entities:

Category and Tag

We are going to create the following four Category entries:

private List<String> categories = List.of(
    "Java",
    "JPA",
    "jOOQ",
    "Spring"
);

for (String category : categories) {
    entityManager.persist(
        new Category().setName(category)
    );
}

So, the category table will look as follows:

| id | name   |
|----|--------|
| 1  | Java   |
| 2  | JPA    |
| 3  | jOOQ   |
| 4  | Spring |

And we also create five Tag entities:

private List<String> tags = List.of(
    "Hibernate",
    "JDBC",
    "JPA",
    "jOOQ",
    "Spring"
);

for (String tag : tags) {
    entityManager.persist(
        new Tag()
            .setName(tag)
    );
}

Therefore, the tag table will look like this:

| id | name      |
|----|-----------|
| 1  | Hibernate |
| 2  | JDBC      |
| 3  | JPA       |
| 4  | jOOQ      |
| 5  | Spring    |

UNION ALL

If we want to concatenate the category and tag tables, we can run the following UNION ALL JPQL query:

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    union all
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

assertEquals(9, topics.size());

And, Hibernate will execute this SQL statement:

SELECT c1_0.name
FROM category c1_0
UNION ALL 
SELECT t1_0.name
FROM tag t1_0

Which generates the following result set:

| name      |
|-----------|
| Java      |
| JPA       |
| jOOQ      |
| Spring    |
| Hibernate |
| JDBC      |
| JPA       |
| jOOQ      |
| Spring    |

The UNION ALL operation simply concatenates two result sets, and the output is basically equivalent to using the concatenate method on the categories and tags collections:

List<String> topics = Stream
    .concat(categories.stream(), tags.stream())
    .toList();

The reason why the result set has duplicate entries is that the category and tag tables have overlapping name column values.

UNION

If we don’t want to get duplicates in our result set, we can use the UNION operation instead:

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    union
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

assertEquals(6, topics.size());

And, Hibernate will execute this SQL statement:

SELECT c1_0.name
FROM category c1_0
UNION
SELECT t1_0.name
FROM tag t1_0

And this time, we get the following result set:

| name      |
|-----------|
| JPA       |
| Java      |
| jOOQ      |
| Hibernate |
| JDBC      |
| Spring    |

The UNION operation first concatenates two result sets, and afterward, it removes the duplicates like this:

List<String> topics = Stream
    .concat(categories.stream(), tags.stream())
    .distinct()
    .toList();

INTERSECT

If we want to get the records that are common to both the categories and tag tables, we can use the INTERSECT operation instead:

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    intersect
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

assertEquals(3, topics.size());

When running the INTERSECT JPQL query, Hibernate will execute the following SQL statement:

SELECT c1_0.name
FROM category c1_0
INTERSECT
SELECT t1_0.name
FROM tag t1_0

And we get the following result set:

| name   |
|--------|
| jOOQ   |
| JPA    |
| Spring |

The INTERSECT SQL operation is equivalent to the following Java Stream processing logic:

List<String> topics = categories
    .stream()
    .filter(tags::contains)
    .distinct()
    .toList();

The INTERSECT set operation is supported by Oracle, SQL Server, PostgreSQL, and MySQL from 8.0.31 onward.

Just like UNION, INTERSECT eliminates duplicates in the result set, so if you need to retain the duplicate entries or if there’s no chance of getting duplicates, then you’d need to use INTERSECT ALL.

EXCEPT

If we want to get the categories records that are not found in the tag table, we can use the EXCEPT operation like this:

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    except
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

assertEquals(1, topics.size());

And Hibernate will run the following SQL statement:

SELECT c1_0.name
FROM category c1_0
EXCEPT
SELECT t1_0.name
FROM tag t1_0

And we get the following result set:

| name |
|------|
| Java |

With Java Streams, the EXCEPT SQL operation can be emulated like this:

List<String> topics = categories
    .stream()
    .filter(Predicate.not(tags::contains))
    .distinct()
    .toList();

The EXCEPT set operation is supported by Oracle, SQL Server, PostgreSQL, and MySQL from 8.0.31 onward.

Just like UNION, EXCEPT eliminates duplicates in the result set, so if you need to retain the duplicate entries or if there’s no chance of getting duplicates, then you’d need to use EXCEPT ALL.

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

And there is more!

You can earn a significant passive income stream from promoting all these amazing products that I have been creating.

If you're interested in supplementing your income, then join my affiliate program.

Conclusion

Prior to Hibernate 6, you could use the UNION, INTERSECT, and EXCEPT set operations only in native SQL queries.

From HIbernate 6 onwards, you can use UNION, INTERSECT, and EXCEPT with both JPQL and Criteria API queries. For Criteria API, you need to use the HibernateCriteriaBuilder extension to enable the extra functionalities that are not supported by Jakarta Persistence.

Transactions and Concurrency Control eBook

4 Comments on “The best way to use UNION, INTERSECT, and EXCEPT with Hibernate

  1. You should add a distinct() operation to both INTERSECT and EXCEPT’s Stream equivalent

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.