SQL UNION operator

Here is a clear, practical guide to the SQL UNION operator, with examples, rules, and best-practice notes.

What SQL UNION operator Does

UNION combines the result sets of two or more SELECT statements into a single result set.

Duplicates are removed (like SELECT DISTINCT).
Columns must be compatible (same number of columns and compatible data types).

Basic Syntax

Example: Combine data from two tables

✔ Removes duplicate rows
✔ Returns one combined result set

UNION ALL (keeps duplicates)

UNION ALL is faster because it does NOT check for duplicates.

Use when:

You want all rows, including duplicates
You want better performance on large datasets

Important Rules

Each SELECT must have the same number of columns
Columns must be in the same order
Columns must be compatible types (e.g., INT with BIGINT, VARCHAR with TEXT)
ORDER BY applies once at the end:

Practical Examples

1. Combine active and archived orders

2. Get a list of unique cities from multiple tables

3. Find all unique tags across two tag tables

🔧 Using UNION inside subqueries

🏎 Performance Tips

✔ Use UNION ALL when duplicates don’t matter—much faster
✔ Apply ORDER BY only at the end
✔ Ensure SELECTs are using indexed columns when possible
✔ Avoid mixing very different data types