Kenhuang Academy
  • English

      • 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