MySQL UNION

Overview

The UNION operator combines the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default.

UNION Syntax

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION vs UNION ALL

UNION

-- Removes duplicates
SELECT country FROM customers
UNION
SELECT country FROM suppliers;

-- Each country appears only once

UNION ALL

-- Keeps all rows including duplicates
SELECT country FROM customers
UNION ALL
SELECT country FROM suppliers;

-- All countries from both tables

Comparison

| Aspect |-------------|-------|-----------| | Duplicates | Performance | Use case | Memory

Basic UNION

Same Number of Columns

-- Combine results from two tables
SELECT id, name FROM active_users
UNION
SELECT id, name FROM inactive_users;

-- Different tables, same structure
SELECT name, email FROM customers
UNION
SELECT name, email FROM contacts;

Different Number of Columns

-- Must have same number of columns
SELECT name, email FROM users
UNION
SELECT name, NULL FROM admins;  -- Added NULL for missing column

Column Types

-- Compatible data types
SELECT id, name FROM users        -- INT, VARCHAR
UNION
SELECT id, name FROM products;    -- INT, VARCHAR (compatible)

UNION with WHERE

-- Add conditions to each SELECT
SELECT id, name, 'Customer' AS type FROM customers WHERE country = 'USA'
UNION
SELECT id, name, 'Supplier' AS type FROM suppliers WHERE country = 'USA';

-- Complex conditions
SELECT id, name, status FROM active_orders WHERE total > 100
UNION
SELECT id, name, 'archived' FROM archive_orders WHERE total > 100;

UNION and ORDER BY

-- Sort entire result set
SELECT id, name, created_at FROM current_data
UNION
SELECT id, name, created_at FROM archive_data
ORDER BY created_at DESC;

-- Sort individual SELECTs
(SELECT id, name FROM active_users ORDER BY created_at DESC LIMIT 10)
UNION
(SELECT id, name FROM new_users ORDER BY created_at DESC LIMIT 10)
ORDER BY name;

UNION with LIMIT

-- Top from each source
(SELECT id, name FROM current_products ORDER BY sales DESC LIMIT 10)
UNION
(SELECT id, name FROM popular_products ORDER BY views DESC LIMIT 10);

-- Limited union result
SELECT id, name FROM large_table
UNION
SELECT id, name FROM another_table
LIMIT 100;

Combining Multiple SELECTs

-- Three-table union
SELECT name FROM current_customers
UNION
SELECT name FROM former_customers
UNION
SELECT name FROM potential_customers;

-- Four-table union
SELECT id, email FROM users
UNION
SELECT id, email FROM subscribers
UNION
SELECT id, email FROM contacts
UNION
SELECT id, email FROM members;

UNION with Subqueries

-- Union with subqueries
SELECT id, name FROM (
    SELECT id, name FROM current_users WHERE active = 1
    UNION
    SELECT id, name FROM legacy_users WHERE status = 'active'
) AS all_active_users;

-- Complex subquery union
SELECT id, total FROM (
    SELECT id, SUM(amount) AS total FROM orders_2023 GROUP BY id
    UNION
    SELECT id, SUM(amount) AS total FROM orders_2024 GROUP BY id
) AS combined_orders;

Practical Examples

Log Aggregation

SELECT id, message, created_at FROM error_logs
UNION
SELECT id, message, created_at FROM warning_logs
UNION
SELECT id, message, created_at FROM info_logs
ORDER BY created_at DESC
LIMIT 100;

Multi-Source Data

SELECT id, name, 'Active' AS status FROM active_users
UNION
SELECT id, name, 'Inactive' AS status FROM inactive_users
UNION
SELECT id, name, 'Pending' AS status FROM pending_users
ORDER BY name;

Data Deduplication

-- Find all unique email addresses
SELECT email FROM customers
UNION
SELECT email FROM suppliers
UNION
SELECT email FROM partners
WHERE email IS NOT NULL;

Time-Based Union

SELECT id, data FROM current_data
UNION
SELECT id, data FROM historical_data
WHERE date >= '2024-01-01'
ORDER BY date DESC;

Performance Considerations

Index Usage

-- Indexes on WHERE columns improve performance
SELECT id FROM orders WHERE status = 'completed'
UNION
SELECT id FROM returns WHERE status = 'approved';

-- Ensure indexes on join/where columns
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_approved ON returns(status);

Query Optimization

-- Use UNION ALL when possible
SELECT id FROM table1 WHERE date > '2024-01-01'
UNION ALL  -- Faster, no deduplication
SELECT id FROM table2 WHERE date > '2024-01-01';

-- Limit results early
(SELECT id FROM large_table ORDER BY date DESC LIMIT 100)
UNION
(SELECT id FROM another_large_table ORDER BY date DESC LIMIT 100);

UNION in Programming Languages

Python / Python

# Union in Python
query = """
    SELECT id, name FROM current_users WHERE active = 1
    UNION
    SELECT id, name FROM archived_users WHERE active = 1
"""
cursor.execute(query)
results = cursor.fetchall()

PHP / PHP

<?php
$query = "
    SELECT id, name, 'Current' AS source FROM current_users
    UNION
    SELECT id, name, 'Archived' AS source FROM archived_users
    ORDER BY name
";
$result = $conn->query($query);
?>

Summary

UNION operator provides:

• Result Combination: Merge multiple SELECT results
• Deduplication: UNION removes duplicates
• Performance: UNION ALL is faster
• Sorting: ORDER BY at the end
• Limitations: Same column count and compatible types
• Use Cases: Log aggregation, multi-source data

---

Previous: LIKE

Next: ORDER BY