UNION
SQL · SET Operators
Theory
UNION stacks the results of two SELECT queries on top of each other and removes duplicate rows. Both queries must return the same number of columns in the same order with compatible types; the column names come from the first query. SELECT name, city FROM employees UNION SELECT name, city FROM contractors gives one list of people, with anyone present in both tables shown once.
Removing duplicates means the engine must sort or hash the combined rows. UNION ALL skips that step and keeps every row, so it is faster and is the right choice when duplicates are impossible or wanted (for example appending monthly tables). Columns are matched by position, not by name, so listing columns in a different order silently mixes data. A single ORDER BY at the end sorts the whole combined result.
Why we use it
Similar data often lives in separate tables: employees and contractors, this year's and last year's orders. UNION combines them into one result so you can analyse them together.
When to use it
Use UNION when you need a combined list without duplicates and UNION ALL when you want every row or know there are none. To combine columns side by side from related tables, use a JOIN instead.
How it works
- Run the first SELECT
- Run the second SELECT
- Same column count and types? Yes: Stack the rows together. No: Error: queries do not line up.
- UNION ALL? Yes: Keep every row. No: Remove duplicate rows.
- Apply ORDER BY to the combined result
Syntax
SELECT columns FROM t1
UNION
SELECT columns FROM t2;Example
SELECT name FROM employees
UNION
SELECT name FROM contractors;Key points
- Both queries need the same number of columns with compatible types
- UNION ALL keeps duplicates and is faster
- Columns are matched by position, not by name
- Result column names come from the first query
- One ORDER BY at the end sorts the whole combined result