1-- UNION: removes duplicates
2SELECT column1, column2 FROM table_a
3UNION
4SELECT column1, column2 FROM table_b;
5
6-- UNION ALL: keeps duplicates
7SELECT column1, column2 FROM table_a
8UNION ALL
9SELECT column1, column2 FROM table_b;

1-- Table: us_customers
2-- | name    | city     |
3-- | Alice   | New York |
4-- | Bob     | Chicago  |
5
6-- Table: eu_customers
7-- | name    | city     |
8-- | Alice   | New York |
9-- | Charlie | London   |
10
11SELECT name, city FROM us_customers
12UNION
13SELECT name, city FROM eu_customers;
14
15-- Result (3 rows — duplicate "Alice, New York" removed):
16-- | name    | city     |
17-- | Alice   | New York |
18-- | Bob     | Chicago  |
19-- | Charlie | London   |

1SELECT name, city FROM us_customers
2UNION ALL
3SELECT name, city FROM eu_customers;
4
5-- Result (4 rows — all rows kept):
6-- | name    | city     |
7-- | Alice   | New York |
8-- | Bob     | Chicago  |
9-- | Alice   | New York |
10-- | Charlie | London   |

1-- UNION: SELECT + SELECT + SORT + DISTINCT
2-- Time complexity: O(n log n) for the deduplication step
3
4-- UNION ALL: SELECT + SELECT (no extra processing)
5-- Time complexity: O(n) — just concatenates results

1-- Check execution plan to see the sort operation
2EXPLAIN
3SELECT id, name FROM employees
4UNION
5SELECT id, name FROM contractors;
6-- Shows a "Sort" or "HashAggregate" node for deduplication
7
8EXPLAIN
9SELECT id, name FROM employees
10UNION ALL
11SELECT id, name FROM contractors;
12-- No sort node — faster execution

1-- 1. Tables have no overlapping data (e.g., partitioned by date)
2SELECT * FROM orders_2024
3UNION ALL
4SELECT * FROM orders_2025;
5
6-- 2. You want to count total rows including duplicates
7SELECT product_id FROM store_a_sales
8UNION ALL
9SELECT product_id FROM store_b_sales;
10-- Use this for "how many total sales across both stores"
11
12-- 3. Each SELECT already has DISTINCT or WHERE ensuring uniqueness
13SELECT DISTINCT customer_id FROM orders WHERE year = 2024
14UNION ALL
15SELECT DISTINCT customer_id FROM orders WHERE year = 2025;

1-- 1. You need a unique list from overlapping sources
2SELECT email FROM newsletter_subscribers
3UNION
4SELECT email FROM registered_users;
5-- Produces a deduplicated email list
6
7-- 2. Combining lookup values from multiple tables
8SELECT status_code, description FROM order_statuses
9UNION
10SELECT status_code, description FROM shipment_statuses;

1-- Chain multiple UNIONs
2SELECT name FROM employees
3UNION ALL
4SELECT name FROM contractors
5UNION ALL
6SELECT name FROM interns;
7
8-- Mix UNION and UNION ALL (use parentheses for clarity)
9(SELECT name FROM employees
10 UNION
11 SELECT name FROM contractors)
12UNION ALL
13SELECT name FROM interns;

1-- Sort the combined result
2SELECT name, salary FROM employees
3UNION ALL
4SELECT name, salary FROM contractors
5ORDER BY salary DESC
6LIMIT 10;
7
8-- To sort individual SELECTs, use subqueries
9SELECT * FROM (
10    SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5
11) AS top_employees
12UNION ALL
13SELECT * FROM (
14    SELECT name, salary FROM contractors ORDER BY salary DESC LIMIT 5
15) AS top_contractors;

1-- Column names come from the FIRST SELECT
2SELECT first_name AS name, hire_date AS date FROM employees
3UNION ALL
4SELECT company_name, contract_date FROM contractors;
5-- Result columns are named "name" and "date"
6
7-- Types must be compatible (implicit casting occurs)
8SELECT id, price FROM products          -- price is DECIMAL
9UNION ALL
10SELECT id, estimated_cost FROM quotes;  -- estimated_cost is FLOAT
11-- Result column is cast to the wider type

1-- UNION: stacks rows vertically (more rows, same columns)
2-- JOIN: combines columns horizontally (same rows, more columns)
3
4-- UNION: "Give me all customers from both tables"
5SELECT name FROM us_customers
6UNION ALL
7SELECT name FROM eu_customers;
8
9-- JOIN: "Give me customer info paired with their orders"
10SELECT c.name, o.total
11FROM customers c
12JOIN orders o ON c.id = o.customer_id;