Set Operations

Joins combine columns horizontally. Set operations combine query results vertically. Despite the name, SQL commonly works with multisets (bags), so duplicate handling must be explicit.

Prerequisites

Be comfortable with projections, compatible data types, NULL, and SQL bag semantics.

Learning goals

• Use UNION, INTERSECT, and EXCEPT with compatible projections.
• Explain the difference between distinct and ALL variants.
• Align columns by position in portable SQL.
• Recognize DuckDB's by-name extension without mistaking it for standard behavior.

Compatibility and duplicates

Each input must return the same number of columns for standard set operations, and corresponding types must be compatible. Columns align by position, not by alias. The final column names generally come from the first query.

UNION removes duplicate result rows; UNION ALL preserves them. Duplicate elimination requires additional work and can erase meaningful event multiplicity. Use the distinct form only when uniqueness is part of the requested result.

Worked example

This Standard SQL query produces a unique contact-name list. Both Ada rows collapse, and the two Eli prospect rows collapse.

SELECT customer_name FROM customers
UNION
SELECT customer_name FROM prospects
ORDER BY customer_name;

customer_name
Ada
Ben
Cy
Dia
Eli

UNION ALL retains bag semantics and therefore every occurrence.

SELECT customer_name FROM customers
UNION ALL
SELECT customer_name FROM prospects
ORDER BY customer_name;

customer_name
Ada
Ada
Ben
Cy
Dia
Eli
Eli

INTERSECT returns rows present in both inputs; EXCEPT returns rows in the left input but not the right. Their ALL forms preserve multiplicity according to multiset rules.

DuckDB-specific: UNION BY NAME and UNION ALL BY NAME align columns by name and fill missing columns with NULL. This can be convenient for evolving files, but portable SQL aligns by position and requires the same number of output columns.

Limitations and common mistakes

• UNION is not a general cure for duplicates; decide which key defines uniqueness.
• Swapping projected column order can silently mix meanings when types are compatible.
• A final ORDER BY applies to the combined result. Individual input ordering does not establish final order.
• NULL rows participate in duplicate elimination under set-operation comparison rules; do not assume ordinary NULL = NULL predicate behavior applies unchanged.

Practice

Use INTERSECT to find names already present in both customers and prospects. Then use EXCEPT to list prospects who are not customers. Predict whether duplicates remain before running each query.

Next steps

Finish the path by learning a repeatable workflow for SQL debugging and query plans.

Primary sources

Checked 2026-10-14: DuckDB set operations, PostgreSQL combining queries, and ISO/IEC 9075-1:2023.