UNION, INTERSECT, and EXCEPT

-- UNION ALL: combine with duplicates (faster)
SELECT 'customer' AS role, customer_id AS id, email
FROM customers
WHERE is_active = true
UNION ALL
SELECT 'employee' AS role, employee_id AS id, email
FROM employees
WHERE employment_status = 'active'
UNION ALL
SELECT 'supplier' AS role, supplier_id AS id, contact_email AS email
FROM suppliers
WHERE is_active = true
ORDER BY role, email;

-- INTERSECT: find customers who are also employees
SELECT email FROM customers
INTERSECT
SELECT email FROM employees;

-- EXCEPT: find customers who never placed an order
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE c.is_active = true
EXCEPT
SELECT o.customer_id, c.first_name, c.last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY customer_id;

-- Combine aggregated results from different periods
SELECT 'Q1' AS quarter, SUM(amount) AS total
FROM sales WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31'
UNION ALL
SELECT 'Q2' AS quarter, SUM(amount) AS total
FROM sales WHERE sale_date BETWEEN '2025-04-01' AND '2025-06-30'
UNION ALL
SELECT 'Q3' AS quarter, SUM(amount) AS total
FROM sales WHERE sale_date BETWEEN '2025-07-01' AND '2025-09-30'
ORDER BY quarter;