SQL: UNION & SET OPERATORS
Master SQL set operations: combine query outputs vertically with UNION, optimize throughput with UNION ALL, find common records with INTERSECT, isolate differences with EXCEPT (MINUS), and enforce sorting rules.
What are SQL Set Operators?
In relational database systems, Set Operators allow you to combine the results of two or more independent SELECT queries into a single unified result set. While a JOIN merges tables horizontally by adding columns based on a key condition, set operators merge query results vertically by appending rows.
SQL defines four standard set operators rooted in classical mathematical set theory:
- UNION: Appends rows from multiple queries and eliminates duplicate rows.
- UNION ALL: Appends rows from multiple queries while preserving all duplicates.
- INTERSECT: Returns only rows that exist in all combined query results.
- EXCEPT (or MINUS): Returns rows from the first query that do not exist in the second query.
Horizontal vs Vertical Composition
- Relational Joins (Horizontal):Combine columns from two tables using a common key predicate (ON a.id = b.a_id).
- Set Operators (Vertical):Stack rows from two queries that return identical column structures into one tabular feed.
- Independent Execution:Each individual SELECT statement in a set operation executes as a standalone query before results are merged.
Rules for Set Operators
Database query engines enforce strict architectural rules before allowing set operations to execute. Both queries must produce compatible schemas.
| Requirement | Rule Specification | Failure Behavior |
|---|---|---|
| Column Count | Every SELECT query must return the exact same number of columns. | Query fails with a syntax or column-mismatch error. |
| Data Type Compatibility | Columns in corresponding positions must have compatible data types (e.g., INT with INT, TEXT with VARCHAR). | Query fails or triggers implicit casting errors. |
| Column Aliases | Final column header names are determined exclusively by the first SELECT query. | Aliases defined in subsequent SELECT statements are ignored. |
| ORDER BY Placement | ORDER BY must appear once at the very end of the compound query. | Individual subqueries cannot contain ORDER BY unless wrapped in parentheses. |
Position-Based Column Matching
- Positional Alignment:The SQL engine matches columns by index position (column 1 with column 1, column 2 with column 2), not by name.
- Explicit Casting:If data types do not align naturally, use CAST(col AS TEXT) to make corresponding positions compatible.
UNION Operator
The UNION operator merges the results of two or more SELECT statements and automatically filters out duplicate rows from the final result set. If a row appears in both query results, it appears only once in the combined output.
In the example below, we compile a list of distinct cities by combining cities where students live with cities associated with enrolled programs. Notice that duplicate cities like London are collapsed into a single entry.
SQL UNION Example
SQLCombine student cities and specific regional offices into a distinct set
Deduplication Cost of UNION
- Sorting Overhead:To eliminate duplicates, the engine must sort the entire combined dataset or populate an in-memory hash table.
- Memory Usage:On large tables with millions of records, distinct sorting can spill to disk temporary files and degrade performance.
UNION ALL Operator
The UNION ALL operator combines the result sets of two or more SELECT statements without removing duplicate rows. Every row returned by each individual query is preserved in the output.
Because UNION ALL skips the deduplication step, it executes in direct $O(N)$ linear streaming time, making it the preferred default choice whenever duplicate removal is not required.
SQL UNION ALL Example
SQLAppend student cities and center locations while preserving all occurrences
When to Use UNION ALL
- Guaranteed Disjoint Sets:When you know inputs cannot overlap (e.g., active orders vs archived orders), always use UNION ALL.
- High-Throughput Ingestion:Streaming logs, audit histories, or time-series partitions should use UNION ALL to avoid sort bottlenecks.
- Aggregating Totals:Calculating sums or counts across unions requires UNION ALL so duplicate values are not lost.
UNION vs UNION ALL
Choosing between UNION and UNION ALL is one of the most practical optimization decisions in relational query design.
| Attribute | UNION | UNION ALL |
|---|---|---|
| Duplicate Retention | Removes duplicates (returns unique rows) | Retains all duplicates exactly as produced |
| Execution Speed | Slower (requires sorting or hash deduplication) | Fast (direct streaming concatenation) |
| Memory Consumption | High (accumulates rows in memory buffers to compare) | Minimal (streams rows directly to consumer) |
| Best Practice Default | Use only when duplicates are unwanted | Default choice for performance |
Direct Comparison: UNION vs UNION ALL
SQLInspect row count differences when executing on identical datasets
Golden Rule of Performance
- Prefer UNION ALL:Start with UNION ALL by default. Switch to UNION only if your application logic specifically requires distinct values.
- Explain Plan Check:In query plans, UNION shows a Distinct Sort or Hash Aggregate node that does not appear in UNION ALL.
INTERSECT Operator
The INTERSECT operator returns only the distinct rows that are present in both query result sets. If a record exists in the first query but is missing from the second, or vice versa, it is excluded.
In the school database example below, we find students who have enrolled in both Databases (Course 1) and Algorithms (Course 2).
SQL INTERSECT Example
SQLFind student IDs who are actively enrolled in two distinct courses
INTERSECT vs INNER JOIN
- Comparison Axis:INTERSECT compares entire projected row tuples, while INNER JOIN links tables on explicit key predicates.
- NULL Handling:Standard ANSI INTERSECT treats NULL values as matching equal values, unlike equality joins where NULL = NULL evaluates to UNKNOWN.
- Deduplication:INTERSECT automatically returns distinct rows even if an element appears multiple times in the source datasets.
EXCEPT / MINUS Operator
The EXCEPT operator (called MINUS in Oracle) returns distinct rows from the first query that do not exist in the second query. It performs a set difference operation.
Unlike UNION and INTERSECT, the order of queries matters significantly for set differences:
Query A EXCEPT Query B -- Rows in A that are NOT in B
Query B EXCEPT Query A -- Rows in B that are NOT in A
SQL EXCEPT Example
SQLIdentify students enrolled in Databases (Course 1) who did not enroll in Algorithms (Course 2)
Database Dialect Differences
- ANSI Standard / SQLite / Postgres / SQL Server:Uses the EXCEPT keyword.
- Oracle Database:Uses the MINUS keyword for identical set subtraction functionality.
- MySQL Support:MySQL added native support for EXCEPT in version 8.0.31; earlier versions emulate it using NOT EXISTS or LEFT JOIN ... WHERE right IS NULL.
Ordering Combined Results
When combining queries using set operators, you cannot place an ORDER BY clause inside each individual SELECT block. Instead, a single ORDER BY clause must be placed at the very end of the entire compound statement.
Because column names for the output are taken exclusively from the first SELECT statement, your ORDER BY clause must reference either the column names (or aliases) from the first query, or use numeric ordinal column positions (such as ORDER BY 1, 2).
Ordering Combined Set Results
SQLConsolidate customer and product records with unified sorting at the end
Sorting Syntax Rules
- Single Trailing Clause:ORDER BY applies to the final merged result set, not to intermediate query parts.
- Column Naming Rule:Reference the alias established in the first SELECT query. Referencing an alias declared only in the second query throws an invalid identifier error.
- Positional Sorting:Using ORDER BY 1 DESC sorts by the first column index in the projected schema.
Chaining Multiple Set Operators and Precedence
You can chain multiple set operators within a single SQL statement. When mixing different set operators (such as combining UNION with INTERSECT), SQL operator precedence dictates the evaluation sequence:
INTERSECThas higher precedence thanUNIONandEXCEPTin standard ANSI SQL.- Operators with equal precedence evaluate from left to right.
- Use parentheses
(...)to explicitly control evaluation order and improve code readability.
Chaining Set Operators with Parentheses
SQLCombine multiple set operations with explicit grouping parentheses
Compound Query Best Practices
- Always Use Parentheses:Parentheses remove ambiguity about whether INTERSECT or UNION executes first.
- Maintain Identical Projection:Ensure every branch inside parenthesized blocks projects matching column counts and datatypes.
Knowledge Check: SQL UNION & Set Operators
1. What is the main operational difference between UNION and UNION ALL?
2. Why does UNION ALL execute significantly faster than UNION on large datasets?
3. Which rule must all queries combined by set operators follow?
4. What does the INTERSECT operator return from two queries?
5. Which operator is Oracle database equivalent for the ANSI SQL EXCEPT operator?
6. Where must the ORDER BY clause be placed in a set operation query?