Skip to main content
Set operations combine the results of two or more SELECT statements. Both queries must produce the same number of columns, and corresponding columns must have compatible types.

​
UNION

Combines two result sets and removes duplicate rows: 
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
Because deduplication requires comparing every row, UNION is slower than UNION ALL. Use it only when you actually need distinct results.

​
UNION ALL

Combines two result sets and keeps all rows, including duplicates: 
SELECT customer_id, total FROM myschema.orders_2024
UNION ALL
SELECT customer_id, total FROM myschema.orders_2025
UNION ALL is faster than UNION because no deduplication step is needed. Prefer it when duplicates are acceptable or when you know the two result sets are already disjoint.

​
EXCEPT

Returns rows from the left query that do not appear in the right query, with duplicates removed: 
SELECT id FROM myschema.all_customers
EXCEPT
SELECT id FROM myschema.opted_out_customers

​
EXCEPT ALL

Like EXCEPT, but preserves multiplicity. If a row appears m times on the left and n times on the right, the result contains max(m - n, 0) copies of that row: 
SELECT product_id FROM myschema.cart_items
EXCEPT ALL
SELECT product_id FROM myschema.backordered_items

​
INTERSECT

Returns rows that appear in both the left and right query, with duplicates removed: 
SELECT id FROM myschema.premium_customers
INTERSECT
SELECT id FROM myschema.active_customers

​
INTERSECT ALL

Like INTERSECT, but preserves multiplicity. If a row appears m times on the left and n times on the right, the result contains min(m, n) copies of that row: 
SELECT product_id FROM myschema.wishlist_items
INTERSECT ALL
SELECT product_id FROM myschema.in_stock_items

​
ORDER BY after set operations

ORDER BY is supported after any set operation. However, you must reference columns by position (1-based) or by alias — not by the original column name from the base tables: 
-- Valid: column position
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
ORDER BY 1, 2

-- Valid: alias defined in the SELECT list
SELECT id, name AS customer_name FROM myschema.active_customers
UNION
SELECT id, name AS customer_name FROM myschema.archived_customers
ORDER BY customer_name
LIMIT and OFFSET / FETCH may follow ORDER BY as usual: 
SELECT id FROM myschema.new_orders
UNION ALL
SELECT id FROM myschema.backfill_orders
ORDER BY 1 DESC
LIMIT 100

​
Chaining Multiple Set Operations

SELECT id, status FROM myschema.orders WHERE region = 'us-east'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'us-west'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'eu-central'
Set operations associate left-to-right. UNION ALL followed by EXCEPT first unions the two queries, then removes rows from the third: 
SELECT id FROM myschema.base_set
UNION
SELECT id FROM myschema.additions
EXCEPT
SELECT id FROM myschema.exclusions

​
Not Supported

  • Parenthesized set operations (e.g., (SELECT ... UNION SELECT ...) UNION SELECT ...)