What is the difference between UNION and UNION ALL in SQL?

-- Get all unique emails from both active and archived users
SELECT email FROM active_users
UNION
SELECT email FROM archived_users;
-- Duplicates are removed (email appearing in both tables shows once)
-- Sorts the result (implicit ORDER BY to detect duplicates)

-- Example
-- active_users:   alice@qa.com, bob@dev.com
-- archived_users: alice@qa.com, charlie@hr.com

SELECT email FROM active_users
UNION
SELECT email FROM archived_users;
-- Result: alice@qa.com, bob@dev.com, charlie@hr.com (3 rows, no duplicate alice)

-- Keep ALL rows including duplicates
SELECT email FROM active_users
UNION ALL
SELECT email FROM archived_users;
-- Result: alice@qa.com, bob@dev.com, alice@qa.com, charlie@hr.com (4 rows)

-- UNION ALL is faster — no deduplication step
-- Use when you know there are no duplicates OR you want to count them

-- Combine this month + last month test results
SELECT test_name, status, run_date FROM test_runs_jan
UNION ALL
SELECT test_name, status, run_date FROM test_runs_feb
ORDER BY run_date DESC;

-- UNION to emulate FULL OUTER JOIN in MySQL
SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

-- Find all product IDs that are either in orders OR in wishlist
SELECT product_id FROM orders
UNION
SELECT product_id FROM wishlist;

-- UNION: extra sort + dedup step → slower
-- UNION ALL: no extra processing → faster

-- Rule: Use UNION ALL when:
-- 1. You know results are already distinct
-- 2. You explicitly want duplicates (e.g., combining log tables)
-- 3. Performance matters on large datasets

-- Use UNION when:
-- 1. You need unique results and can't guarantee no duplicates

-- Columns must be compatible but can have different names
SELECT first_name AS full_name, 'employee' AS type FROM employees
UNION ALL
SELECT company_name AS full_name, 'client' AS type FROM clients
ORDER BY full_name;