Set Operations

Set operators combine the result sets of two or more queries. T-SQL supports UNION, UNION ALL, INTERSECT, and EXCEPT. All four require the inputs to have the same number of columns and compatible data types.

UNION vs UNION ALL

-- Quick concatenation (no dedupe needed)
SELECT employee_id, 'EMP' AS source FROM employees
UNION ALL
SELECT contractor_id, 'CON'         FROM contractors;

-- DISTINCT cleanup (slower)
SELECT email FROM employees
UNION
SELECT email FROM customers;

INTERSECT

Returns rows present in both inputs (deduplicated).

-- Email addresses that belong to BOTH an employee and a customer
SELECT email FROM employees
INTERSECT
SELECT email FROM customers;

EXCEPT

Returns rows in the first input that are not in the second (deduplicated). The MS T-SQL spelling of MINUS.

-- Employees who are NOT also customers
SELECT email FROM employees
EXCEPT
SELECT email FROM customers;

EXCEPT is great for diffing two snapshots:

-- What rows are in current but not in archive?
SELECT * FROM employees_current
EXCEPT
SELECT * FROM employees_archive;

-- What rows are in archive but not in current (deletions)?
SELECT * FROM employees_archive
EXCEPT
SELECT * FROM employees_current;

Column Compatibility

The columns in each branch must align by position and have compatible types. Names come from the first SELECT.

-- The output columns are named id and label
SELECT employee_id AS id, first_name AS label FROM employees
UNION ALL
SELECT department_id, department_name        FROM departments;

ORDER BY at the End

ORDER BY applies to the combined result, not to individual branches. Place it after the final SELECT:

SELECT customer_name AS party FROM customers
UNION
SELECT first_name + ' ' + last_name FROM employees
ORDER BY party;

NULL Semantics in Set Operations

Unlike WHERE, set operators treat NULL = NULL as TRUE for the purpose of comparing rows. Two rows that are NULL in matching columns are considered duplicates by UNION, equal by INTERSECT, and "matched" by EXCEPT.

-- These two queries return one row each, demonstrating NULL=NULL in set ops
SELECT NULL AS x
INTERSECT
SELECT NULL;            -- 1 row

SELECT NULL AS x
EXCEPT
SELECT NULL;            -- 0 rows

This is why EXCEPT works as a robust table-diff tool — WHERE a.col = b.col would miss NULL/NULL matches.

Combining Multiple Set Operators

Precedence: INTERSECT binds tighter than UNION/EXCEPT. Use parentheses to be explicit:

-- Safer, explicit version
(SELECT email FROM employees
 INTERSECT
 SELECT email FROM customers)
UNION
SELECT email FROM partners;

Common Pitfalls

• Hidden DISTINCT cost: UNION materializes a sort or hash; on large result sets this dominates.
• Column count mismatch: Adding a new column to one side without adjusting the other breaks the query immediately at parse time.
• Implicit conversions: If types don't match exactly, SQL Server coerces — and one side may suffer a silent precision loss.
• ORDER BY in branches: You can't put ORDER BY in any branch except by wrapping it in SELECT ... FROM (... ORDER BY ... OFFSET 0 ROWS) sub.

-- ERROR: ORDER BY only allowed on final query
SELECT TOP 10 * FROM employees ORDER BY hire_date  -- not allowed here
UNION ALL
SELECT TOP 10 * FROM contractors;

-- Workaround: wrap each branch as a subquery
SELECT * FROM (
    SELECT TOP 10 * FROM employees ORDER BY hire_date
) e
UNION ALL
SELECT * FROM (
    SELECT TOP 10 * FROM contractors ORDER BY hire_date
) c;

Best Practices

• Default to UNION ALL — only use UNION when dedupe is required.
• Use EXCEPT to diff two row sets including NULL columns.
• Wrap with parentheses when mixing UNION and INTERSECT to control precedence.
• Watch for type and column-count mismatches when refactoring either branch.

Summary

• UNION ALL is fast (no dedupe); UNION adds a DISTINCT step.
• INTERSECT returns rows in both inputs; EXCEPT returns rows in the first but not the second.
• Set operators treat NULL = NULL as equal, unlike standard =.
• Output column names come from the first SELECT; ORDER BY applies once at the end.