Set operations

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.

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.

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.

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.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

UNION ALL

INTERSECT

EXCEPT

​UNION ALL

​INTERSECT

​EXCEPT

UNION ALL

INTERSECT

EXCEPT