beginner

UNION ALL Operator in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 ALL is orders of magnitude faster than UNION.
  • 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 BY and LIMIT correctly 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:

  1. Identical Column Count: Each SELECT statement must project the exact same number of columns.
  2. Compatible Data Types: Columns in the same position must have compatible or castable data types (e.g., INT and BIGINT, or VARCHAR(50) and VARCHAR(100)).
  3. Column Names Taken from First Query: The column names and aliases of the final result set are determined exclusively by the first SELECT statement.
sql
-- 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:

sql
-- 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:

sql
-- 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_sales and february_sales), using UNION wastes CPU and memory checking for duplicates that cannot exist. Always use UNION 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:

  1. Deduplication Cost: UNION performs distinct deduplication. The database engine must either build an in-memory hash table of all projected rows or execute an external sort.
  2. Streaming Execution: UNION ALL simply 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.
  3. Data Loss: UNION will 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

Challenge: Unified Customer Activity Feed

You have a signups table (user_id, signup_time) and a logins table (user_id, login_time). Write a query using UNION ALL that creates a single activity stream containing user_id, event_time, and an event_type column ('SIGNUP' or 'LOGIN'). Sort the combined activity stream chronologically descending.

DPA

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.