# Union / Concat / Intersect / Except

> Combines two row-queries over the same entity with SQL set semantics: Union dedups, Concat keeps duplicates (UNION ALL), Intersect keeps rows present in both sides, Except keeps left rows not in the right. One SQL statement over the entity.

<!-- id: query-set-operators · area: query · stability: stable · html: https://osysharp.com/reference/query/set-operators/ -->

## Summary        {#summary}
The LINQ set operators combine **two row-queries over the same entity** with SQL set semantics. `Union`
is set union (duplicates removed), `Concat` appends (duplicates kept — SQL `UNION ALL`), `Intersect`
keeps rows present in **both** sides, `Except` keeps left rows **not** in the right. The operands run as
one SQL statement — `(left SELECT) UNION [ALL] / INTERSECT / EXCEPT (right SELECT)`.

## Signature      {#signature}
```osy syntax
<rows>.Union(<rows>)      // set union — duplicates removed
<rows>.Concat(<rows>)     // append — duplicates kept (UNION ALL)
<rows>.Intersect(<rows>)  // rows in BOTH sides
<rows>.Except(<rows>)     // left rows NOT in the right
```
where `<rows>` is an entity set or an entity-row chain (`Where` / `OrderBy` / `Skip` / `Take` /
`Distinct`) — both sides the **same entity**.

## Description    {#description}
Semantics are exactly C# / EF:

| Operator | SQL | Duplicates |
|---|---|---|
| `Union` | `UNION` | removed |
| `Concat` | `UNION ALL` | kept — a row matching both sides appears twice |
| `Intersect` | `INTERSECT` | removed |
| `Except` | `EXCEPT` | removed |

- Entity rows dedup by their full column list — effectively **by Id** (rows are PK-unique), which is the
  C# result for object sequences.
- Each operand may carry its own `Where` / `OrderBy` / `Skip` / `Take` / `Distinct` chain. A paged
  operand is parenthesized in the SQL, so its `ORDER BY` / `LIMIT` stays scoped to that side.
- A **bare entity set** is a valid operand (the whole set): `Tag.Where(p).Union(Tag)`.
- **Read-your-own-writes:** uncommitted `new T{}` rows fold into the result per the op's semantics — a
  ghost matching either side joins a `Union`; one matching both sides appears twice in `Concat`, joins an
  `Intersect`, and is excluded by `Except`.
- **Durable:** the read memoizes like any query — a resume does not re-run it.

### Boundaries (pointed diagnostics)   {#boundaries}
- **Operands must be the same entity** — `Order.Union(Customer)` is a compile error (C# requires a
  common element type).
- **Rows only** — no `Select` projection on either side, and no `GroupBy`/`SelectMany`/`Join` before the
  operator.
- **One pair per expression** — `A.Union(B).Union(C)` is refused (`chained set operators are not
  supported yet`).
- **No clauses over the combined set** — `A.Union(B).Count()` / `.OrderBy(…)` refuse with
  `materialize with .ToList() first`.
- **No `Include`** on either side — apply includes after materializing.

## Examples       {#examples}
```osy title="all four operators" test app=query-set-ops
entity Tag {
  [MaxLength(50)] string Label;
  [MaxLength(10)] string Grp;
}

// Union — duplicates removed (a row matching both sides appears once).
Tag[] ActiveOrGroupB() {
  return Tag.Where(t => t.Label != "archived").Union(Tag.Where(t => t.Grp == "b")).ToList();
}

// Concat — duplicates kept (UNION ALL).
Tag[] Appended() { return Tag.Where(t => t.Grp == "a").Concat(Tag.Where(t => t.Grp == "b")).ToList(); }

// Intersect — rows present in BOTH sides.
Tag[] Both() { return Tag.Where(t => t.Label != "x").Intersect(Tag.Where(t => t.Grp == "b")).ToList(); }

// Except — left rows NOT in the right side.
Tag[] LeftOnly() { return Tag.Where(t => t.Label != "x").Except(Tag.Where(t => t.Grp == "b")).ToList(); }

// A bare entity set is a valid operand (the whole set).
Tag[] All() { return Tag.Where(t => t.Grp == "a").Union(Tag).ToList(); }
```

## See also       {#see-also}
- [Distinct](https://osysharp.com/reference/query/distinct/) — per-side and standalone row dedup
- [Where / Single / Count](https://osysharp.com/reference/query/where/) — the operand chains
- [ToList](https://osysharp.com/reference/query/tolist/) — materializing the combined set
- [LINQ over a local list](https://osysharp.com/reference/query/in-memory-linq/) — the same set operators over a local `List`/`HashSet` (identical spellings)