SQL Querying

Set Operations

Set operations combine compatible query results vertically. UNION, INTERSECT, and EXCEPT use distinct semantics by default, while their ALL forms preserve bag multiplicities according to each operator.

Operands must have the same degree, and corresponding columns must have compatible types and meanings.
UNION removes duplicates; UNION ALL preserves every occurrence.
INTERSECT keeps rows appearing in both inputs; EXCEPT keeps left rows absent from the right input.
ALL uses occurrence counts.
Column names normally come from the first query, while a final ORDER BY applies to the combined result.
Parenthesize mixed set operators so precedence and any branch-local ordering or limiting are explicit.

Compatibility and multiplicity

Set-operation semantics
Operator	Rows retained	For counts m=3, n=2	Typical intent
`UNION`	Distinct rows in either input	1 copy	Combine unique customer IDs
`UNION ALL`	Every occurrence in both inputs	5 copies	Append compatible event streams
`INTERSECT`	Distinct rows in both inputs	1 copy	Customers in both campaigns
`INTERSECT ALL`	Minimum occurrence count	2 copies	Bag intersection
`EXCEPT`	Distinct left rows absent right	0 copies	Catalog IDs not in a feed
`EXCEPT ALL`	Positive left-minus-right count	1 copy	Bag difference

Combine customers reached through two channels

SELECT customer_id
FROM orders
WHERE status = 'PAID'
UNION
SELECT customer_id
FROM orders
WHERE status = 'REFUNDED'
ORDER BY customer_id;

If customer 7 has two paid orders and one refunded order, the first branch produces two 7s and the second one 7. UNION returns one 7; UNION ALL returns three. Choosing between them is a business rule about occurrences, not a performance toggle.