Joins Updated November 2026

UNION and UNION ALL

UNION appends the rows of one SELECT to another and removes duplicate rows; UNION ALL appends them and keeps duplicates, which is faster. Both queries must return the same number of columns with compatible types, and the column names come from the first query. ORDER BY goes once, at the very end.

Syntax

SELECT cols FROM a UNION [ALL] SELECT cols FROM b [ORDER BY col];

Examples

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

Result: Every distinct email from both tables

SELECT 'customer' AS kind, name FROM customers
UNION ALL
SELECT 'employee', name FROM employees
ORDER BY name;

Result: kind | name customer | Ana employee | Ana customer | Ben

A literal label column tells you which table each row came from. UNION ALL keeps the two Anas.

SELECT country FROM customers
EXCEPT
SELECT country FROM suppliers;

Result: Countries that have customers but no suppliers

EXCEPT (set difference) and INTERSECT (common rows) follow the same rules as UNION. Both work in SQLite and PostgreSQL.

Common mistakes

Different column counts raise an error (SELECTs to the left and right of UNION do not have the same number of result columns); pad with NULL or literals.
Putting ORDER BY on the first SELECT instead of the end is a syntax error; only one ORDER BY is allowed, after the last query.
Using UNION when duplicates are impossible or wanted; it sorts and dedupes needlessly, so use UNION ALL for performance.

Practice it

The drills on Querydrill check your answer on live data, free, no account needed.

Start practicing

Questions

What is the difference between UNION and JOIN?

JOIN combines columns from two tables side by side on a key. UNION combines rows from two queries vertically; the tables need not be related at all.

When should I use UNION ALL?

Whenever you do not need duplicates removed: the two sources are already disjoint, or you want every row counted. It avoids an expensive sort and is the default choice.

FULL OUTER JOINFULL OUTER JOIN returns all rows from both tables, matched where possible and NULL elsewhere. Syntax, rows present on only one side, and the UNION workaround.
SELECTHow to write a SQL SELECT: choose columns from a table, select everything with *, compute expressions, and combine it with WHERE, ORDER BY and LIMIT.
ORDER BYORDER BY sorts the rows of a query result. Learn ASC and DESC, sorting on multiple columns, sorting by an expression or alias, and where NULLs end up.
DISTINCTSELECT DISTINCT returns each distinct value or combination once. List unique countries, count distinct values, and see why DISTINCT dedupes whole rows.