UNION ALL Operator in SQL
Overview
The UNION ALL operator combines the result sets of two or more SELECT queries into a single unified result set. Unlike the standard UNION operator—which performs an expensive deduplication step by sorting or hashing all rows—UNION ALL concatenates the outputs directly, retaining all duplicate records. In modern big data and enterprise SQL pipelines, UNION ALL is the standard default for combining partitioned tables, log streams, and parallel pipelines.
What You Will Learn
In this lesson, you will master:
- UNION vs UNION ALL: Why
UNION ALLis orders of magnitude faster thanUNION. - Schema Compatibility Rules: Column count matching, positional alignment, and implicit type casting.
- Combining Multi-Region & Partitioned Tables: Assembling global unified datasets.
- Sorting & Paginating Unified Results: Placing
ORDER BYandLIMITcorrectly on combined queries. - Memory & Spilling Mechanics: Avoiding unnecessary sort and hash spills in high-volume queries.
Performance Benchmark: UNION vs UNION ALL
The fundamental difference between UNION and UNION ALL lies in how duplicates are handled:
Query 1 Output: [ A, B, C, D ]
Query 2 Output: [ C, D, E, F ]
UNION ALL: Concatenates immediately without checking duplicates
Result: [ A, B, C, D, C, D, E, F ] --> Instant streaming ($O(1)$ memory)
UNION: Performs in-memory Hash Deduplication or External Merge Sort
Result: [ A, B, C, D, E, F ] --> Heavy CPU, memory buffer, disk spills
Performance Rule of Thumb
Unless business requirements strictly mandate that duplicate rows must be removed, always prefer UNION ALL over UNION. When merging two tables with 10 million rows each, UNION ALL streams results instantly, while UNION can take minutes sorting 20 million rows in memory.
Schema & Structural Requirements
All queries combined by UNION ALL must follow three strict rules:
- Identical Column Count: Each
SELECTstatement must project the exact same number of columns. - Compatible Data Types: Columns in the same position must have compatible or castable data types (e.g.,
INTandBIGINT, orVARCHAR(50)andVARCHAR(100)). - Column Names Taken from First Query: The column names and aliases of the final result set are determined exclusively by the first
SELECTstatement.
-- Valid UNION ALL combining active orders and archived orders
SELECT
order_id,
customer_id,
order_total,
ordered_at,
'ACTIVE' AS source_table
FROM active_orders
UNION ALL
SELECT
order_id,
customer_id,
order_total,
ordered_at,
'ARCHIVED' AS source_table
FROM archived_orders
ORDER BY ordered_at DESC;
Proper Placement of ORDER BY & LIMIT
When sorting the unified results, the ORDER BY clause belongs at the very end of the combined query. You cannot place an ORDER BY inside individual SELECT queries without parentheses:
-- CORRECT: Sorts the entire combined result set
SELECT product_id, price FROM online_products
UNION ALL
SELECT product_id, price FROM retail_products
ORDER BY price DESC
LIMIT 50;
-- Top 5 from each source, then combined (Requires Parentheses):
(SELECT product_id, price, 'Online' AS channel
FROM online_products
ORDER BY price DESC
LIMIT 5)
UNION ALL
(SELECT product_id, price, 'Retail' AS channel
FROM retail_products
ORDER BY price DESC
LIMIT 5)
ORDER BY price DESC;
Combining Unbalanced Schemas: NULL Padding
When the tables you are combining have different structures, pad the missing columns with NULL:
-- Combining web visits with in-store visits
SELECT
visit_id,
user_id,
visit_timestamp,
ip_address, -- Web only
NULL AS store_location -- Padded for in-store
FROM web_traffic_logs
UNION ALL
SELECT
visit_id,
user_id,
visit_timestamp,
NULL AS ip_address, -- Padded for web
store_location -- In-store only
FROM physical_store_logs;
Common Mistakes & Gotchas
- Positional Mapping Bugs: SQL matches columns by position, not by name! If Query 1 selects
(email, phone)and Query 2 selects(phone, email), SQL will silently merge the phone numbers into the email column if both are strings. - Using UNION when Inputs are Guaranteed Disjoint: If you are combining tables that cannot possibly share rows (e.g.,
january_salesandfebruary_sales), usingUNIONwastes CPU and memory checking for duplicates that cannot exist. Always useUNION ALL.
Interview Perspective
SQL Interview Question: Why is UNION ALL preferred over UNION, and what happens under the hood during a UNION operation?
Key Talking Points for Candidates:
- Deduplication Cost:
UNIONperforms distinct deduplication. The database engine must either build an in-memory hash table of all projected rows or execute an external sort. - Streaming Execution:
UNION ALLsimply streams rows from the first query and then streams rows from the second query with zero intermediate buffering, yielding near-zero memory footprint and immediate time-to-first-row. - Data Loss:
UNIONwill silently swallow duplicate rows that may be legitimate business transactions (e.g., two identical purchases of $10 at the exact same timestamp).
Interactive Practice Challenge
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.