UNION Operator
Overview
The UNION operator combines the result sets of two or more SELECT queries into a single result set, automatically removing duplicate rows.
Learning Objectives
- Combine query outputs vertically using
UNION. - Understand structural constraints: matching column counts and compatible datatypes.
- Analyze the performance overhead of
UNIONdeduplication sorting.
Detailed Concept Explanation
UNION stacks query result sets vertically (combining rows, whereas JOIN combines columns side-by-side).
Rules for Set Operators:
- Each
SELECTstatement must return the same number of columns. - Corresponding columns must have compatible data types.
- Column names in the final output are inherited from the first
SELECTquery.
Because UNION automatically eliminates duplicate rows across query sets, the database engine executes a memory-intensive deduplication sort operation under the hood.
Code Examples
SQL
-- Combine client and partner contacts into a unique list
SELECT name, email, 'Client' AS source FROM clients
UNION
SELECT name, email, 'Partner' AS source FROM partners;
Best Practices
- Use UNION ALL when Duplicates are Impossible: If you know the two datasets do not overlap (or duplicate removal is unnecessary), use
UNION ALLinstead to avoid the expensive deduplication sort step.
Interview Perspective
Note
Interview Question: What is the main difference between UNION and UNION ALL?
UNION removes duplicate rows between query results (requiring a distinct sort operation), whereas UNION ALL retains all rows including duplicates (executing faster without sorting).
Interactive Challenges
Summary
UNION stacks query rows vertically while automatically deduplicating rows.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.