UNION & UNION ALL

Beginner

Joins & Set Ops

UNION deduplicates results from two SELECT statements; UNION ALL is faster (no dedup); both require matching column count and compatible types.

Overview

UNION combines the result sets of two or more SELECT statements. UNION deduplicates rows (like SELECT DISTINCT on the combined result); UNION ALL returns all rows including duplicates and is significantly faster because it skips the deduplication sort. Both require the same number of columns and compatible data types in each SELECT. UNION is used for combining partitioned data, merging data from different tables with the same structure, or building report queries without JOINs.

UNION vs UNION ALL

UNION removes duplicate rows — it internally sorts or hashes the combined result set, which is expensive. UNION ALL appends results without deduplication. Use UNION ALL whenever you know duplicates are impossible or acceptable.

SQL — UNION vs UNION ALL

-- UNION — deduplicates (sorts combined result)
SELECT id, name, 'current' AS source FROM products
UNION
SELECT id, name, 'archive' AS source FROM products_archive;
-- Removes exact duplicate rows across both queries

-- UNION ALL — no deduplication (faster)
SELECT id, name, 'UK' AS region FROM products_uk
UNION ALL
SELECT id, name, 'EU' AS region FROM products_eu
UNION ALL
SELECT id, name, 'US' AS region FROM products_us;
-- All rows returned including any duplicates

-- Performance tip:
-- UNION:     requires sort/hash to detect duplicates → O(N log N)
-- UNION ALL: simple append → O(N)
-- Always prefer UNION ALL unless duplicate removal is required

-- Column aliases — only first SELECT's aliases appear in result
SELECT id AS product_id, name AS product_name FROM products
UNION ALL
SELECT id, name FROM archived_products;  -- aliases from first SELECT are used

Ordering and Limiting UNION Results

ORDER BY and LIMIT applied to the entire UNION must appear at the end, outside of individual SELECT statements. Each individual SELECT cannot have its own ORDER BY (unless wrapped in a subquery).

SQL — ORDER BY and LIMIT on UNION results

-- ORDER BY on the entire UNION result
SELECT id, name, 'active' AS status FROM products WHERE active = TRUE
UNION ALL
SELECT id, name, 'inactive' AS status FROM products WHERE active = FALSE
ORDER BY name ASC;           -- applies to combined result

-- LIMIT on combined result
SELECT id, name FROM products
UNION ALL
SELECT id, name FROM archived_products
ORDER BY id
LIMIT 100 OFFSET 0;

-- Per-SELECT ORDER BY inside subqueries (workaround)
SELECT * FROM (
    SELECT id, name, total FROM orders WHERE status = 'PLACED' ORDER BY total DESC LIMIT 5
) top_placed
UNION ALL
SELECT * FROM (
    SELECT id, name, total FROM orders WHERE status = 'SHIPPED' ORDER BY total DESC LIMIT 5
) top_shipped;

Common UNION Use Cases

UNION is useful for querying partitioned tables, building reports from multiple status groups, combining a live table with an archive table, or creating "super-unions" for full-text fallback results.

SQL — practical UNION use cases

-- Use case 1: combine partitioned archive tables
SELECT id, created_at, total FROM orders_2023
UNION ALL
SELECT id, created_at, total FROM orders_2024
UNION ALL
SELECT id, created_at, total FROM orders_2025
ORDER BY created_at DESC LIMIT 1000;

-- Use case 2: "top N from each category" without window functions
SELECT product_id, category, revenue FROM (
    SELECT product_id, 'Electronics' AS category, SUM(total) AS revenue
    FROM order_items oi JOIN products p ON oi.product_id = p.id
    WHERE p.category = 'Electronics'
    GROUP BY product_id
    ORDER BY revenue DESC LIMIT 5
) e
UNION ALL
SELECT product_id, category, revenue FROM (
    SELECT product_id, 'Books' AS category, SUM(total) AS revenue
    FROM order_items oi JOIN products p ON oi.product_id = p.id
    WHERE p.category = 'Books'
    GROUP BY product_id
    ORDER BY revenue DESC LIMIT 5
) b;

Key Points to Remember

1UNION deduplicates combined rows (expensive); UNION ALL appends without dedup (fast).
2Always use UNION ALL unless you explicitly need duplicate removal.
3All SELECT statements must have the same number of columns with compatible types.
4Column names in the result come from the first SELECT statement.
5ORDER BY and LIMIT apply to the full combined result — place them at the end.
6UNION is useful for querying sharded tables, time-partitioned archives, or multi-status reports.

Interview Questions

What is the difference between UNION and UNION ALL?

EasyInfosys

Why is UNION ALL generally preferred over UNION for performance?

EasyTCS

Can you use ORDER BY in individual SELECT statements of a UNION?

MediumAmazon

What constraints must both SELECT statements satisfy in a UNION?

EasyWipro

When would you use UNION instead of a JOIN?

MediumFlipkart

Ask Aria about UNION & UNION ALL

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

HAVING Clause

Window Functions