# Set operations

Combine two queries with UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL.

## Overview

Set operations combine the result sets of two compatible `Query()` builders. Both queries must return the same model type `T`.

## Syntax

Call `.Union()`, `.UnionAll()`, `.Intersect()`, `.IntersectAll()`, `.Except()`, or `.ExceptAll()` on a query builder, passing a second `TableQueryBuilder` as the right operand:

```csharp
var activeQuery  = User.Query(x => x.IsActive == true);
var premiumQuery = User.Query(x => x.IsPremium == true);

await foreach (var user in activeQuery
    .Union(premiumQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Username);
}
```

## Available operations

| Method | SQL |
|--------|-----|
| `.Union(other)` | `UNION` (deduplicated) |
| `.UnionAll(other)` | `UNION ALL` (includes duplicates) |
| `.Intersect(other)` | `INTERSECT` (deduplicated) |
| `.IntersectAll(other)` | `INTERSECT ALL` (includes duplicates) |
| `.Except(other)` | `EXCEPT` (deduplicated) |
| `.ExceptAll(other)` | `EXCEPT ALL` (includes duplicates) |

## UNION ALL example

```csharp
var recentQuery = Order.Query(x => x.CreatedAt > DateTime.UtcNow.AddDays(-7));
var pendingQuery = Order.Query(x => x.Status == "pending");

await foreach (var order in recentQuery
    .UnionAll(pendingQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{order.Id}: {order.Status}");
}
```

## Parameter handling

Both sub-queries share a single `DbCommand`. Parameters from the left and right queries are offset automatically, so there are no conflicts. You can safely reuse the same parameter values in both queries.

## Compiled query interface

Every generated `Query()` builder implements `ICompiledQuery` internally, which is how its compiled SQL and parameters are merged into the combined command. Any builder can serve as either the left or right operand:

```csharp
// either query can be on either side
await foreach (var row in leftQuery.Intersect(rightQuery).WithConnection(conn).ExecuteAsync())
{
    // ...
}
```

## Limitations

- Both operands must return the same model type `T`.
- Set operations do not currently support chaining a `JOIN` builder on either operand.
- The set-operation builder supports `Limit()` and `Offset()` on the combined result. `ORDER BY` is not available. Sort in C# after collecting results if ordering is required.