UNION vs UNION ALL in SQL: Which One and Why

Both stack the results of two queries on top of each other. The only difference is what happens to duplicates: UNION removes them, UNION ALL keeps them. That one difference decides both the answer you get and how long the query takes.

Everything below runs against the sample database, so you can see the row counts change as you switch between them.

The difference in two queries

The customers table has a city, the departments table has a location. Stack them:

SELECT city AS place FROM Customers
UNION ALL
SELECT location FROM Departments;

Every row from both queries, in order. Now the same thing with UNION:

SELECT city AS place FROM Customers
UNION
SELECT location FROM Departments;

One row fewer, because one place appears in both tables and UNION collapsed the two copies into one. That is the entire difference. Everything else on this page follows from it.

What UNION costs to do that

To know whether a row is a duplicate, the database has to compare every row against every row it has already produced — which means sorting or hashing the combined result before it can return anything. UNION ALL can stream rows out as it finds them.

The rules both must obey

Two queries can only be stacked if their result shapes line up:

1. The same number of columns. Non-negotiable.
2. Compatible types, column by column. Position matters, names do not — the first column of the second query lands under the first column of the first query whatever it is called.
3. The column names come from the first query. Alias there if you want a readable heading; aliasing in the second query has no effect on the output.

Break the first rule and a real database refuses the query outright, with a message about the operands not having the same number of columns:

SELECT city FROM Customers
UNION
SELECT dept_id, dept_name FROM Departments;

That block is marked as intentionally wrong rather than given a Run button, and for an honest reason: the in-browser engine here is lenient about it and returns something anyway, while PostgreSQL, MySQL, SQL Server and Oracle all reject it. Trust the real databases — matching the column lists is a hard rule.

Where ORDER BY goes

One ORDER BY, at the very end, applying to the combined result:

SELECT city AS place FROM Customers
UNION
SELECT location FROM Departments
ORDER BY place;

An ORDER BY inside either branch is either an error or pointless, because the branches are combined before the final sort. The same goes for LIMIT: to limit the combined result, put it after the last branch; to limit a branch, wrap that branch in a subquery.

The pattern you will actually use: labelled UNION ALL

Stacking two different kinds of thing into one list, with a column saying which is which, is the most common real use of UNION ALL:

SELECT first_name AS person_name, 'Employee' AS source FROM Employees
UNION ALL
SELECT contact,               'Customer'         FROM Customers
ORDER BY source, person_name;

Two things worth copying from that query. The literal 'Employee' and 'Customer' are ordinary expressions, so a constant is a perfectly good column. And UNION ALL is correct here rather than a habit: if an employee and a customer happen to share a first name, they are two different people and both rows must survive. UNION would silently merge them — a real bug, not a tidier list.

UNION versus a JOIN

This is the follow-up interview question, and the answer is a single sentence: a join adds columns, a union adds rows.

If you need a customer’s name next to their order, that is a join. If you need this month’s orders and last month’s archived orders in one list, that is a union. They are not alternatives.

INTERSECT and EXCEPT

The same family, less often used and worth knowing exist. INTERSECT returns the rows that appear in both results; EXCEPT (called MINUS in Oracle) returns the rows in the first result that are not in the second. Both de-duplicate, like UNION. MySQL only added them in version 8.0.31, so on older MySQL the equivalents are an EXISTS or NOT EXISTS subquery, which is usually clearer anyway.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• One combined contact listMedium
• Which countries do we sell to?Easy
• How many customers do we have?Easy

See all 42 exercises →

Frequently asked questions

Related reading

• SQL JOINs explained: INNER, LEFT, RIGHT and FULL
• SELECT DISTINCT: removing duplicate rows from results
• EXISTS vs IN: which to use and when
• Where SQL dialects disagree
• 25 SQL interview questions and answers