SQL UNION and UNION ALL Explained

A support team lead asks for a single list of all email addresses used across the platform, both by customers and by employees, for a company-wide notification. The data lives in two separate tables. There is no shared key to join on, and no need to match rows side by side. SQL UNION is the right tool: stack both result sets into one.

How UNION Works

UNION sits between two SELECT statements. It runs both queries, combines the results into one table, and removes duplicate rows. Both queries must return the same number of columns, in the same order, with compatible data types.

The basic structure looks like this:

SELECT column FROM first_table
UNION
SELECT column FROM second_table;

Here is a working example. Return a single deduplicated list of all email addresses from both customers and employees:

SELECT c.email FROM customers c
UNION
SELECT e.email FROM employees e;

SQL runs both SELECT statements, stacks the rows, then removes any email that appears in both tables. Each email address appears once in the result, regardless of which table it came from.

The column name in the output comes from the first SELECT. The second SELECT's column name is ignored. If you want a specific name in the output, alias the column in the first query.

UNION ALL: Keeping Every Row

When duplicates are not a concern, or when you specifically need to count all rows including duplicates, UNION ALL is the better choice. It skips the deduplication step and returns every row from both queries.

Return all email addresses from both tables, including any that appear in both:

SELECT c.email FROM customers c
UNION ALL
SELECT e.email FROM employees e;

The output includes every row from both queries. If the same email exists in customers and employees, it appears twice. Use UNION ALL when you know duplicates are not possible, or when you want to count or audit all rows before deduplication.

Combining Multiple Columns

UNION is not limited to a single column. You can combine any number of columns, as long as the count and types match on both sides.

Return a combined list of first_name and last_name from both customers and employees, deduplicated:

SELECT c.first_name, c.last_name FROM customers c
UNION
SELECT e.first_name, e.last_name FROM employees e;

Each row is now a name pair. Deduplication applies to the full row, not individual columns. A row is a duplicate only if every column value matches another row exactly.

Sorting the Combined Result

ORDER BY applies to the full combined result and must appear at the very end of the statement, after the last SELECT. It references column names from the first SELECT.

Return all email addresses from both tables, sorted alphabetically:

SELECT c.email FROM customers c
UNION
SELECT e.email FROM employees e
ORDER BY email;

You cannot place ORDER BY between the two SELECT statements. It must come after the final query in the chain.

How This Connects to Other Concepts

UNION is one of four [SET operators](learn/sql-set-operators) in SQL. The others are UNION ALL, INTERSECT, and EXCEPT. If you need rows that appear in both result sets, use INTERSECT. If you need rows from one set that are absent from another, use EXCEPT.

UNION is not the same as a [JOIN](learn/sql-joins). A JOIN combines columns horizontally by matching rows on a shared key. UNION stacks rows vertically and requires matching column structure. They solve different problems.

Common Mistakes

1. Mismatched column count between the two SELECT statements

Both sides of a UNION must return the same number of columns. A mismatch causes a syntax error before any rows are returned.

-- wrong: left returns 2 columns, right returns 1
SELECT c.first_name, c.last_name FROM customers c
UNION
SELECT e.first_name FROM employees e;

Match the column count on both sides, adding a second column to whichever SELECT is short.

-- correct
SELECT c.first_name, c.last_name FROM customers c
UNION
SELECT e.first_name, e.last_name FROM employees e;

2. Placing ORDER BY between the two queries

ORDER BY inserted between the two SELECT statements causes a syntax error in most SQL databases. It must come at the end.

-- wrong: ORDER BY between the queries
SELECT c.email FROM customers c
ORDER BY email
UNION
SELECT e.email FROM employees e;

Move ORDER BY to the end of the full statement so it sorts the entire combined result.

-- correct
SELECT c.email FROM customers c
UNION
SELECT e.email FROM employees e
ORDER BY email;

3. Using UNION when UNION ALL is more appropriate

UNION deduplicates by sorting or hashing the full result. On large tables this adds unnecessary overhead when duplicates either cannot exist or do not matter.

-- wrong: unnecessary deduplication on distinct tables with no overlap
SELECT c.customer_id FROM customers c
UNION
SELECT o.order_id FROM orders o;

Use UNION ALL when the data sources have no overlap or when you need every row regardless of duplicates.

-- correct
SELECT c.customer_id FROM customers c
UNION ALL
SELECT o.order_id FROM orders o;

Quick Recap

• UNION combines two SELECT results into one and removes duplicate rows from the output.
• UNION ALL combines two SELECT results and keeps all rows, including duplicates.
• Both SELECT statements must return the same number of columns in the same order with compatible data types.
• Column names in the output come from the first SELECT statement, not the second.
• ORDER BY applies to the full combined result and must be placed at the very end of the statement.
• Use UNION ALL by default when duplicates are not a concern; switch to UNION only when deduplication is actually needed.

SELECT c.email FROM customers c
UNION
SELECT e.email FROM employees e;

SELECT c.email FROM customers c
UNION ALL
SELECT e.email FROM employees e;

SELECT c.first_name, c.last_name FROM customers c
UNION
SELECT e.first_name, e.last_name FROM employees e
ORDER BY last_name ASC;

SELECT c.first_name, c.last_name FROM customers c
WHERE c.country = 'Germany'
UNION
SELECT e.first_name, e.last_name FROM employees e
WHERE e.department = 'Sales';

SELECT c.email FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o)
UNION
SELECT e.email FROM employees e
WHERE e.department = 'Marketing'
ORDER BY email ASC;