# Set operations

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

## Overview

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

---

## Syntax

Call `.Union()`, `.UnionAll()`, `.Intersect()`, or `.Except()` on a query builder, passing a second query as an `ICompiledQuery`:

```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 use the same parameter values in both queries.

---

## Compiled query interface

Set operations rely on the `ICompiledQuery` interface, which the generated query builders implement. Any generated `Query()` builder can participate as either the left or right operand:

```csharp
// Both operands are ICompiledQuery — either 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 on the set-operation builder; sort in C# after collecting results if ordering is required.