Kenhuang Academy
  • English

      • PostgreSQL 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 is useful when you need to combine data from multiple tables or queries with similar structures.

      Basic Syntax

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

      UNION vs UNION ALL

      UNION (Removes Duplicates)

      -- Combines results and removes duplicates
SELECT id, name FROM current_users
UNION
SELECT id, name FROM archived_users;

      UNION ALL (Keeps Duplicates)

      -- Combines results and keeps all rows (faster)
SELECT id, name FROM current_users
UNION ALL
SELECT id, name FROM archived_users;

      Rules for UNION

      1. Same number of columns: All SELECT statements must have the same number of columns
      2. Compatible data types: Corresponding columns must have compatible data types
      3. Column order: Columns are matched by position, not by name
      4. Column names: Result uses column names from the first SELECT
      -- Valid UNION
SELECT id, name, email FROM users
UNION
SELECT id, full_name, contact_email FROM customers;

-- Invalid UNION (different number of columns)
SELECT id, name FROM users
UNION
SELECT id, name, email FROM customers;  -- Error!

      Basic UNION Examples

      Combining Similar Tables

      -- Combine active and inactive users
SELECT id, name, 'active' AS status FROM active_users
UNION
SELECT id, name, 'inactive' AS status FROM inactive_users;

-- Combine current and historical data
SELECT order_id, customer_id, order_date FROM current_orders
UNION
SELECT order_id, customer_id, order_date FROM archived_orders
ORDER BY order_date DESC;

      Combining Different Tables

      -- Combine employees and contractors
SELECT 
    id,
    name,
    email,
    'employee' AS type
FROM employees
UNION
SELECT 
    id,
    name,
    email,
    'contractor' AS type
FROM contractors;

      UNION with WHERE Clause

      -- Filter before combining
SELECT id, name, email FROM users WHERE country = 'USA'
UNION
SELECT id, name, email FROM customers WHERE country = 'USA';

-- Different filters for each query
SELECT id, name, 'premium' AS tier FROM users WHERE subscription = 'premium'
UNION
SELECT id, name, 'basic' AS tier FROM users WHERE subscription = 'basic'
ORDER BY name;

      UNION with ORDER BY

      -- ORDER BY applies to the entire result set
SELECT name, email FROM users
UNION
SELECT name, email FROM customers
ORDER BY name;

-- Order by column position
SELECT name, email, created_at FROM users
UNION
SELECT name, email, created_at FROM customers
ORDER BY 3 DESC;  -- Order by third column (created_at)

-- Order with alias
SELECT name, email, created_at AS registration_date FROM users
UNION
SELECT name, email, created_at FROM customers
ORDER BY registration_date DESC;

      UNION with LIMIT

      -- LIMIT applies to the entire result set
SELECT name, email FROM users
UNION
SELECT name, email FROM customers
ORDER BY name
LIMIT 100;

-- Limit individual queries (use subqueries)
(SELECT name, email FROM users ORDER BY created_at DESC LIMIT 10)
UNION
(SELECT name, email FROM customers ORDER BY created_at DESC LIMIT 10);

      Multiple UNION Operations

      -- Combine three or more queries
SELECT id, name FROM employees
UNION
SELECT id, name FROM contractors
UNION
SELECT id, name FROM consultants;

-- Mix UNION and UNION ALL
SELECT id, name FROM current_users
UNION ALL
SELECT id, name FROM archived_users
UNION  -- This UNION removes duplicates from the combined result
SELECT id, name FROM deleted_users;

      UNION with Aggregates

      -- Combine aggregated results
SELECT 'Users' AS source, COUNT(*) AS count FROM users
UNION
SELECT 'Customers' AS source, COUNT(*) AS count FROM customers
UNION
SELECT 'Vendors' AS source, COUNT(*) AS count FROM vendors;

-- Monthly statistics
SELECT 
    'January' AS month,
    SUM(amount) AS total
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1
UNION
SELECT 
    'February' AS month,
    SUM(amount) AS total
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 2
ORDER BY month;

      UNION with Subqueries

      -- Combine results from subqueries
SELECT user_id, total_spent FROM (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY user_id
) AS q1
UNION
SELECT customer_id, total_spent FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM invoices
    WHERE invoice_date >= '2024-01-01'
    GROUP BY customer_id
) AS q2;

      UNION with JOIN

      -- Combine joined results
SELECT 
    u.name,
    o.order_number,
    'order' AS type
FROM users u
INNER JOIN orders o ON u.id = o.user_id
UNION
SELECT 
    c.name,
    i.invoice_number,
    'invoice' AS type
FROM customers c
INNER JOIN invoices i ON c.id = i.customer_id;

      UNION with CASE

      -- Conditional UNION
SELECT 
    id,
    name,
    CASE 
        WHEN status = 'active' THEN 'Active User'
        ELSE 'Inactive User'
    END AS status_label
FROM users
UNION
SELECT 
    id,
    name,
    'Customer' AS status_label
FROM customers;

      Practical Examples

      Combining Search Results

      -- Search across multiple tables
SELECT 
    id,
    name,
    email,
    'user' AS source
FROM users
WHERE name ILIKE '%john%' OR email ILIKE '%john%'
UNION
SELECT 
    id,
    company_name AS name,
    contact_email AS email,
    'company' AS source
FROM companies
WHERE company_name ILIKE '%john%' OR contact_email ILIKE '%john%'
ORDER BY name;

      Creating Reports

      -- Monthly sales report
SELECT 
    'Q1' AS quarter,
    SUM(amount) AS total_sales
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
UNION
SELECT 
    'Q2' AS quarter,
    SUM(amount) AS total_sales
FROM orders
WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'
UNION
SELECT 
    'Q3' AS quarter,
    SUM(amount) AS total_sales
FROM orders
WHERE order_date BETWEEN '2024-07-01' AND '2024-09-30'
UNION
SELECT 
    'Q4' AS quarter,
    SUM(amount) AS total_sales
FROM orders
WHERE order_date BETWEEN '2024-10-01' AND '2024-12-31';

      Combining Historical and Current Data

      -- All transactions (current and archived)
SELECT 
    transaction_id,
    user_id,
    amount,
    transaction_date,
    'current' AS data_source
FROM current_transactions
WHERE transaction_date >= '2024-01-01'
UNION ALL
SELECT 
    transaction_id,
    user_id,
    amount,
    transaction_date,
    'archived' AS data_source
FROM archived_transactions
WHERE transaction_date >= '2024-01-01'
ORDER BY transaction_date DESC;

      Creating Lookup Lists

      -- All contact methods
SELECT DISTINCT email AS contact, 'email' AS type FROM users WHERE email IS NOT NULL
UNION
SELECT DISTINCT phone AS contact, 'phone' AS type FROM users WHERE phone IS NOT NULL
UNION
SELECT DISTINCT mobile AS contact, 'mobile' AS type FROM users WHERE mobile IS NOT NULL
ORDER BY type, contact;

      Performance Considerations

      UNION vs UNION ALL

      -- UNION (slower - removes duplicates)
SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;

-- UNION ALL (faster - keeps duplicates)
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

      Use UNION ALL when:

      • You know there are no duplicates
      • You want to keep duplicates
      • Performance is critical

      Use UNION when:

      • You need to remove duplicates
      • Data quality requires unique results

      Indexing

      -- Create indexes on columns used in WHERE clauses
CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_customers_country ON customers(country);

-- Query will benefit from indexes
SELECT id, name FROM users WHERE country = 'USA'
UNION
SELECT id, name FROM customers WHERE country = 'USA';

      Using EXPLAIN

      -- Analyze UNION query performance
EXPLAIN ANALYZE
SELECT id, name FROM users
UNION
SELECT id, name FROM customers;

      Common Patterns

      Deduplication Across Tables

      -- Find unique emails across all tables
SELECT email FROM users
UNION
SELECT email FROM customers
UNION
SELECT email FROM subscribers;

      Combining Partial Results

      -- Combine results from different time periods
SELECT * FROM sales_2023
UNION ALL
SELECT * FROM sales_2024;

      Creating Summary Rows

      -- Add a total row to results
SELECT category, SUM(amount) AS total FROM sales GROUP BY category
UNION
SELECT 'TOTAL' AS category, SUM(amount) AS total FROM sales
ORDER BY category;

      Best Practices

      1. Use UNION ALL when possible for better performance

        -- Faster if you know there are no duplicates
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

      2. Ensure column compatibility

        -- Good: Same data types
SELECT id::INTEGER, name::TEXT FROM table1
UNION
SELECT id::INTEGER, name::TEXT FROM table2;

      3. Use meaningful column names

        -- Good: Clear column names from first SELECT
SELECT id AS user_id, name AS user_name FROM users
UNION
SELECT id, name FROM customers;

      4. Filter before UNION

        -- Good: Filter in each SELECT
SELECT id, name FROM users WHERE active = true
UNION
SELECT id, name FROM customers WHERE active = true;

      5. Use parentheses for complex queries

        (SELECT id, name FROM users ORDER BY created_at DESC LIMIT 10)
UNION
(SELECT id, name FROM customers ORDER BY created_at DESC LIMIT 10);

      Common Mistakes to Avoid

      -- ❌ Different number of columns
SELECT id, name FROM users
UNION
SELECT id, name, email FROM customers;  -- Error!

-- ✅ Same number of columns
SELECT id, name, NULL AS email FROM users
UNION
SELECT id, name, email FROM customers;

-- ❌ ORDER BY in individual SELECT (without parentheses)
SELECT id, name FROM users ORDER BY name  -- Error!
UNION
SELECT id, name FROM customers;

-- ✅ ORDER BY after UNION
SELECT id, name FROM users
UNION
SELECT id, name FROM customers
ORDER BY name;

-- ✅ ORDER BY in individual SELECT (with parentheses)
(SELECT id, name FROM users ORDER BY name LIMIT 10)
UNION
(SELECT id, name FROM customers ORDER BY name LIMIT 10);

      Summary

      UNION operator key points:

      • UNION: Combines results and removes duplicates
      • UNION ALL: Combines results and keeps duplicates (faster)
      • All SELECT statements must have:
        • Same number of columns
        • Compatible data types
        • Columns matched by position
      • ORDER BY applies to the entire result set
      • Use UNION ALL when duplicates are not an issue
      • Filter data before UNION for better performance