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:

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.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW