MySQL UNION

Key UNION Concepts

SELECT id FROM table1
UNION
SELECT id FROM table2;

SELECT col1, col2
UNION
SELECT col1, col2;

-- Both must be strings or numbers
SELECT name, age UNION ...

SELECT name AS full_name
UNION SELECT customer_name;

🔹 Basic UNION Syntax

UNION combines multiple SELECT statements vertically, stacking results on top of each other. Each SELECT must return the same number of columns with compatible data types. The column names from the first SELECT statement are used in the final result set.

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

-- UNION with multiple queries
SELECT name, city FROM customers
UNION
SELECT name, city FROM suppliers
UNION
SELECT name, city FROM partners;

🔹 Practical UNION Example

Let's combine customer and supplier contact information into a single directory. This demonstrates how UNION merges data from different sources while maintaining data integrity by removing duplicate entries, creating a unified contact list for business operations.

-- Create sample tables
CREATE TABLE customers (
    id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    type VARCHAR(50)
);

CREATE TABLE suppliers (
    id INT,
    company_name VARCHAR(100),
    contact_email VARCHAR(100),
    category VARCHAR(50)
);

-- Insert sample data
INSERT INTO customers VALUES
(1, 'John Smith', '[email protected]', 'Retail'),
(2, 'Jane Doe', '[email protected]', 'Wholesale');

INSERT INTO suppliers VALUES
(1, 'ABC Corp', '[email protected]', 'Materials'),
(2, 'John Smith', '[email protected]', 'Parts');

-- UNION to create unified contact list
SELECT 
    name AS contact_name,
    email,
    'Customer' AS source
FROM customers
UNION
SELECT 
    company_name,
    contact_email,
    'Supplier' AS source
FROM suppliers
ORDER BY contact_name;

🔹 UNION with WHERE and ORDER BY

You can filter individual SELECT statements with WHERE clauses before combining them with UNION. The ORDER BY clause must be placed at the end of the entire UNION query to sort the final combined result set, not individual queries.

-- UNION with filtering
SELECT product_name, price, 'Electronics' AS category
FROM electronics
WHERE price > 100
UNION
SELECT product_name, price, 'Furniture' AS category
FROM furniture
WHERE price > 100
ORDER BY price DESC;

-- UNION with conditions
SELECT employee_name, department
FROM employees
WHERE status = 'Active'
UNION
SELECT contractor_name, assigned_dept
FROM contractors
WHERE contract_end > CURDATE()
ORDER BY employee_name;

🔹 Real-World Use Cases

UNION is perfect for consolidating data from multiple sources into unified reports. Common applications include creating master contact lists from customers and vendors, combining current and archived records for historical analysis, merging product catalogs from different warehouses, and generating comprehensive reports from departmental databases.

-- Use Case 1: Combine current and archived orders
SELECT order_id, customer_name, order_date, 'Current' AS status
FROM current_orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
UNION
SELECT order_id, customer_name, order_date, 'Archived' AS status
FROM archived_orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
ORDER BY order_date DESC;

-- Use Case 2: Create unified product search
SELECT product_id, product_name, price, 'In Stock' AS availability
FROM warehouse_a
UNION
SELECT product_id, product_name, price, 'In Stock' AS availability
FROM warehouse_b
ORDER BY product_name;