> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Set operations

> Set operations combine compatible result shapes. Inputs must have the same number of columns and compatible types. Use aliases in the first input to name the result.

ReferenceApplies to: Unified engine deployments

Set operations combine compatible result shapes. Inputs must have the same number of columns and compatible types. Use aliases in the first input to name the result.

| Operator | Behavior |
| - | - |
| `UNION` | Combine and remove duplicate rows |
| `UNION ALL` | Combine and keep duplicates |
| `INTERSECT` | Rows present in both inputs |
| `EXCEPT` or `MINUS` | Rows present in the left input but absent from the right |

## UNION ALL

UNION ALL preserves duplicates and is appropriate when source rows should remain distinct observations. ORDER BY after the combined relation controls final order.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT item_key, source_group
FROM (
  SELECT i_item_sk AS item_key, 'first' AS source_group
  FROM demo.tpcds.item WHERE i_item_sk <= 3
  UNION ALL
  SELECT i_item_sk AS item_key, 'second' AS source_group
  FROM demo.tpcds.item WHERE i_item_sk BETWEEN 2 AND 4
) AS combined
ORDER BY item_key, source_group;
```

## INTERSECT

This finds item keys common to both ranges.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk FROM (
  SELECT i_item_sk FROM demo.tpcds.item WHERE i_item_sk <= 5
  INTERSECT
  SELECT i_item_sk FROM demo.tpcds.item WHERE i_item_sk BETWEEN 3 AND 7
) AS common_items
ORDER BY i_item_sk;
```

## EXCEPT

Input order matters: reversing the inputs changes the result.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk FROM (
  SELECT i_item_sk FROM demo.tpcds.item WHERE i_item_sk <= 5
  EXCEPT
  SELECT i_item_sk FROM demo.tpcds.item WHERE i_item_sk <= 3
) AS remaining_items
ORDER BY i_item_sk;
```

Parenthesize mixed set operations to make precedence explicit. UNION BY NAME is unavailable. For predictable presentation, apply ordering to the final combined result, not just an individual input.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.