UNION Is Making Your Query Three Times Slower for No Reason

You typed UNION out of habit. It quietly re-sorted 8 million rows to remove duplicates that were never there.

The problem

Combine customer lists from two tables (legacy and new) into a single list.

The one difference that matters

UNION must sort or hash the entire result to find duplicates. On 8 million rows that is seconds versus milliseconds.

Common mistakes

1. Mismatched column counts

-- Error
SELECT name, email FROM legacy_customers
UNION ALL
SELECT name FROM new_customers;

Both sides need the same number of columns in the same data types, in order. Pad the short side with a constant:

SELECT name, NULL AS email FROM new_customers;

2. Column names come from the first branch

Aliases in the second branch are ignored. To rename a column, rename it in the first branch.

3. ORDER BY in the wrong place

-- Sorts only the first branch, or errors, depending on the engine
SELECT name FROM legacy_customers ORDER BY name
UNION ALL
SELECT name FROM new_customers;

-- Correct: ORDER BY applies to everything, placed last
SELECT name FROM legacy_customers
UNION ALL
SELECT name FROM new_customers
ORDER BY name;

4. Using UNION instead of a LEFT JOIN

If you are UNION-ing two nearly identical queries that differ only in a condition, there is usually a single CASE WHEN or LEFT JOIN that is shorter and far faster.

Two under-used relatives

-- Rows present in both
SELECT id FROM table_a INTERSECT SELECT id FROM table_b;

-- Rows in A but not in B
SELECT id FROM table_a EXCEPT SELECT id FROM table_b;

EXCEPT (called MINUS in Oracle) is the shortest anti-join when you only need to compare one column.

Practice

• LeetCode 1965 — Employees With Missing Information
• LeetCode 1581 — Customer Who Visited but Did Not Make Any Transactions
• LeetCode 1741 — Find Total Time Spent by Each Employee
• HackerRank — The Report