Combining Results: UNION and UNION ALL

The Two Systems

We had an old database (`legacy_orders`) and a new one (`modern_orders`). For a historical report, I needed to show orders from *both* systems in a single list.

The Quest: Vertical Combination

`UNION` stacks the results of two (or more) queries on top of each other.

The Implementation: The Syntax

SELECT order_id, order_date, amount FROM legacy_orders

UNION

SELECT order_id, order_date, amount FROM modern_orders;

UNION vs UNION ALL

**UNION**: Removes duplicates. Slower (requires sorting).

**UNION ALL**: Keeps all rows, including duplicates. Faster.

-- Use UNION ALL if you don't expect duplicates or don't care

SELECT * FROM table_a

UNION ALL

SELECT * FROM table_b;

The Rules

1. Both queries must have the **same number of columns**.

2. The columns must have **compatible data types**.

The "Oops" Moment

I forgot to match the column order. The first query returned (id, date, amount), the second returned (id, amount, date). The data was scrambled.

**Pro Tip**: Even if SQL doesn't throw an error, double-check that your columns are aligned. Use explicit column names in both SELECTs.

The Victory

The VP could now see a 10-year history of orders, seamlessly combined from two different systems. UNION is the bridge between old and new.

Your Task for Today

Combine data from two related tables using UNION. Check the performance difference between `UNION` and `UNION ALL`.

*Day 45: Difference and Intersection—EXCEPT and INTERSECT.*