MySQL UNION ALL

Key UNION ALL Concepts

SELECT id FROM table1
UNION ALL
SELECT id FROM table2;

-- Faster than UNION
UNION ALL

-- All rows included
SELECT * UNION ALL SELECT *;

-- Count includes duplicates
COUNT(*) with UNION ALL

🔹 Basic UNION ALL Syntax

UNION ALL works exactly like UNION but includes all rows from all SELECT statements without removing duplicates. This makes it more efficient when you know there are no duplicates or when you specifically need to keep duplicate records for accurate counting or analysis.

-- Basic UNION ALL syntax
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

-- Multiple queries with UNION ALL
SELECT name, amount FROM sales_2023
UNION ALL
SELECT name, amount FROM sales_2024
UNION ALL
SELECT name, amount FROM sales_2025;

🔹 UNION vs UNION ALL Comparison

Understanding the difference between UNION and UNION ALL is crucial for query optimization. UNION removes duplicates which requires additional processing time, while UNION ALL simply appends all results together. Choose UNION ALL when duplicates are acceptable or when tables have no overlapping data for better performance.

-- Sample data
CREATE TABLE orders_q1 (order_id INT, customer VARCHAR(50));
CREATE TABLE orders_q2 (order_id INT, customer VARCHAR(50));

INSERT INTO orders_q1 VALUES (1, 'John'), (2, 'Jane'), (3, 'John');
INSERT INTO orders_q2 VALUES (3, 'John'), (4, 'Mike'), (5, 'Jane');

-- Using UNION (removes duplicates)
SELECT customer FROM orders_q1
UNION
SELECT customer FROM orders_q2;
-- Result: John, Jane, Mike (3 rows)

-- Using UNION ALL (keeps duplicates)
SELECT customer FROM orders_q1
UNION ALL
SELECT customer FROM orders_q2;
-- Result: John, Jane, John, John, Mike, Jane (6 rows)

🔹 Practical UNION ALL Example

UNION ALL is perfect for combining transaction logs or historical data where every record matters. This example shows how to create a complete sales report by combining data from multiple years, preserving all transactions including repeat customers for accurate revenue calculations and trend analysis.

-- Combine sales from multiple years
SELECT 
    sale_id,
    customer_name,
    amount,
    sale_date,
    '2023' AS year
FROM sales_2023
UNION ALL
SELECT 
    sale_id,
    customer_name,
    amount,
    sale_date,
    '2024' AS year
FROM sales_2024
UNION ALL
SELECT 
    sale_id,
    customer_name,
    amount,
    sale_date,
    '2025' AS year
FROM sales_2025
ORDER BY sale_date DESC;

🔹 When to Use UNION ALL

Choose UNION ALL when performance matters and duplicates are acceptable or necessary. It's ideal for log aggregation, time-series data combination, audit trails, and situations where you need exact record counts. Use UNION when data integrity requires unique records or when duplicate elimination is essential for accurate reporting.

-- Use Case 1: Aggregate logs from multiple servers
SELECT log_time, message, 'Server1' AS source
FROM server1_logs
WHERE log_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
UNION ALL
SELECT log_time, message, 'Server2' AS source
FROM server2_logs
WHERE log_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY log_time DESC;

-- Use Case 2: Combine inventory from warehouses
SELECT 
    product_id,
    quantity,
    'Warehouse A' AS location
FROM warehouse_a_inventory
UNION ALL
SELECT 
    product_id,
    quantity,
    'Warehouse B' AS location
FROM warehouse_b_inventory;

🔹 Performance Considerations

UNION ALL is significantly faster than UNION because it skips the duplicate removal step. When working with large datasets or when you know tables don't have overlapping data, always prefer UNION ALL. The performance difference becomes more noticeable as dataset size increases, making UNION ALL the better choice for big data operations.

-- Fast: Use UNION ALL when no duplicates exist
SELECT id, name FROM active_users
UNION ALL
SELECT id, name FROM inactive_users;

-- Slower: UNION checks for duplicates unnecessarily
SELECT id, name FROM active_users
UNION
SELECT id, name FROM inactive_users;

-- Best practice: Use UNION ALL with DISTINCT if needed
SELECT DISTINCT * FROM (
    SELECT id, name FROM table1
    UNION ALL
    SELECT id, name FROM table2
) AS combined;