---
title: "Feature: #104631 - Add UNION Clause support to the QueryBuilder"
manual: "TYPO3 Core Changelog"
version: "main"
permalink: "https://docs.typo3.org/permalink/changelog:feature-104631-1723714985"
source: "Changelog/13.3/Feature-104631-AddUNIONClauseSupportToTheQueryBuilder.rst"
modified: "2026-09-15T10:46:36+00:00"
---

# Feature: #104631 - Add `UNION Clause` support to the QueryBuilder

See [forge#104631](https://forge.typo3.org/issues/104631)

## Description

The `UNION` clause is used to combine the result sets of two or more
`SELECT` statements, which all database vendors support, each with their
own specific variations.

However, there is a commonly shared subset that works across all of them:

```sql
SELECT column_name(s) FROM table1
WHERE ...

UNION <ALL | DISTINCT>

SELECT column_name(s) FROM table2
WHERE ...

ORDER BY ...
LIMIT x OFFSET y
```

with shared requirements:

-   Each SELECT must return the same fields in number, naming and order.
-   Each SELECT must not have ORDER BY, expect MySQL allowing it to be used as sub
    query expression encapsulated in parentheses.

Generic `UNION` clause support has been contributed to `Doctrine DBAL` and
is included since [Release 4.1.0](https://github.com/doctrine/dbal/releases/tag/4.1.0)
which introduces two new API method on the
`QueryBuilder`:

-   `union(string|QueryBuilder $part)` to create first UNION query part
-   `addUnion(string|QueryBuilder $part, UnionType $type = UnionType::DISTINCT)`
    to add additional `UNION (ALL|DISTINCT)` query parts with the selected union
    query type.

TYPO3 decorates the Doctrine DBAL `QueryBuilder`
to provide for most API methods automatic
quoting of identifiers and values **and**  to apply database restrictions automatically
for `SELECT` queries.

The Doctrine DBAL API has been adopted now to provide the same surface for the
TYPO3 `\TYPO3\CMS\Core\Database\Query\QueryBuilder` and the intermediate
`\TYPO3\CMS\Core\Database\Query\ConcreteQueryBuilder` to make it easier to
create `UNION` clause queries. The API on both methods allows to provide
dedicated `QueryBuilder` instances
or direct queries as strings in case it is needed.

> [!NOTE]
> Providing `UNION` parts as plain string requires the developer to take
> care of proper quoting and escaping within the query part.

In queries containing subqueries, only named placeholders (such as `:username`)
can be used and must be registered on the outermost
`QueryBuilder` object,
similar to advanced query creation with `SUB QUERIES`.

> [!WARNING]
> `QueryBuilder` can be used create
> `UNION` clause queries not compatible with all database providers,
> for example using `LIMIT/OFFSET` in each part query or other stuff.

### UnionType::DISTINCT and UnionType::ALL

Each subsequent part needs to be defined either as `UNION DISTINCT` or
`UNION ALL` which could have not so obvious effects.

For example, using `UNION ALL` for all parts in between except for the last
one would generate larger result sets first, but discards duplicates when adding
the last result set. On the other side, using `UNION ALL` tells the query
optimizer **not** to scan for duplicates and remove them at all which can be a
performance improvement - if you can deal with duplicates it can be ensured that
each part does not produce same outputs.

### Example: Compose a `UNION` clause query

**Custom service class using a UNION query to retrieve data.**

```php
use Doctrine\DBAL\Query\UnionType;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

final readonly class MyService {
  public function __construct(
    private ConnectionPool $connectionPool,
  ) {}

  public function executeUnionQuery(
    int $pageIdOne,
    int $pageIdTwo,
  ): ?array {
    $connection = $this->connectionPool->getConnectionForTable('pages');
    $unionQueryBuilder = $connection->createQueryBuilder();
    $firstPartQueryBuilder = $connection->createQueryBuilder();
    $secondPartQueryBuilder = $connection->createQueryBuilder();
    // removing automatic TYPO3 restriction for the sake of the example
    // to match the PLAIN SQL example when executed. Not removing them
    // will generate corresponding restriction SQL code for each part.
    $firstPartQueryBuilder->getRestrictions()->removeAll();
    $secondPartQueryBuilder->getRestrictions()->removeAll();
    $expr = $unionQueryBuilder->expr();

    $firstPartQueryBuilder
      // The query parts **must** have the same column counts, and these
      // columns **must** have compatible types
      ->select('uid', 'pid', 'title')
      ->from('pages')
      ->where(
        $expr->eq(
          'pages.uid',
          // !!! Ensure to use most outer / top / main QueryBuilder
          //   instance for creating parameters and the complete
          //   query can be executed in the end.
          $unionQueryBuilder->createNamedParameter($pageIdOne, Connection::PARAM_INT),
        )
      );
    $secondPartQueryBuilder
      ->select('uid', 'pid', 'title')
      ->from('pages')
      ->where(
        $expr->eq(
          'pages.uid',
          // !!! Ensure to use most outer / top / main QueryBuilder instance
          $unionQueryBuilder->createNamedParameter($pageIdTwo, Connection::PARAM_INT),
        )
      );

    // Set first and second union part to the main (union)
    // QueryBuilder and return the retrieved rows.
    return $unionQueryBuilder
      ->union($firstPartQueryBuilder)
      ->addUnion($secondPartQueryBuilder, UnionType::DISTINCT)
      ->orderBy('uid', 'ASC')
      ->executeQuery()
      ->fetchAllAssociative();
  }
}
```

This would create the following query for MySQL with `$pageIdOne = 100` and
`$pageIdTwo = 10`:

```sql
    (SELECT `uid`, `pid`, `title` FROM pages WHERE `pages`.`uid` = 100)
UNION
    (SELECT `uid`, `pid`, `title` FROM pages WHERE `pages`.`uid` = 10)
ORDER BY `uid` ASC
```

## Impact

Extension authors can use the new
`QueryBuilder` methods to build more
advanced queries.