UNION, INTERSECT, EXCEPT

Learning Focus

Use this lesson to combine result sets from multiple queries using set operators in DuckDB.

Set Operators Overview

Operator	Description
UNION	All rows from both queries, duplicates removed
UNION ALL	All rows from both queries, duplicates kept
INTERSECT	Only rows present in both queries
EXCEPT	Rows in the first query not present in the second

Rules

• Both queries must return the same number of columns
• Column types must be compatible
• Column names come from the first query

UNION and UNION ALL

-- All employees from currently active + archived tables (no duplicates)
SELECT id, name, salary FROM employees
UNION
SELECT id, name, salary FROM employees_archive;

-- Keep all rows including duplicates (faster)
SELECT id, name FROM employees
UNION ALL
SELECT id, name FROM contractors;

INTERSECT

-- Employees who are ALSO in the managers table
SELECT id FROM employees
INTERSECT
SELECT employee_id FROM managers;

EXCEPT

-- Employees who have NOT been assigned to any project
SELECT id FROM employees
EXCEPT
SELECT employee_id FROM project_assignments;

Practical Use Case: Data Quality Check

-- Find IDs in source but not in destination (migration validation)
SELECT id FROM source_employees
EXCEPT
SELECT id FROM target_employees;

UNION with ORDER BY and LIMIT

-- Must wrap in subquery to ORDER the combined result
SELECT * FROM (
    SELECT name, salary FROM employees
    UNION ALL
    SELECT name, salary FROM contractors
) combined
ORDER BY salary DESC
LIMIT 10;

Common Pitfalls

Pitfall	Consequence	Prevention
UNION removes duplicates silently	Lost data	Use UNION ALL unless dedup is intentional
Column type mismatch	Error or implicit cast	Align types with CAST()
ORDER BY inside UNION subquery	Not standard SQL	Move ORDER BY outside the union

Quick Reference

SELECT col FROM a UNION     SELECT col FROM b;  -- dedup
SELECT col FROM a UNION ALL SELECT col FROM b;  -- keep all
SELECT col FROM a INTERSECT SELECT col FROM b;  -- common rows
SELECT col FROM a EXCEPT    SELECT col FROM b;  -- in a, not in b

What's Next

• Next: Grouping and Filtering - Use GROUP BY, HAVING, and advanced grouping sets.
• Module Overview - Return to this module index.