SQL UNION Operator: UNION vs UNION ALL with Examples

Two tables hold the same kind of row, and the report needs one list covering both. A join widens each row with the other table's columns. What you want here is one result set stacked on another. UNION runs both queries and returns their rows as one result, with repeats removed unless you write UNION ALL.

What the SQL UNION operator does

UNION appends the result of the second query to the result of the first, and then eliminates duplicate rows in the same way DISTINCT does (PostgreSQL 17 documentation). It is the operator for a current table and its archive, for two regional tables with the same columns, and for one report that has to draw its rows from more than one place.

The examples run against two small tables with the same shape:

CREATE TABLE employees (
    name       VARCHAR(50),
    department VARCHAR(50),
    salary     DECIMAL(10, 2)
);

CREATE TABLE contractors (
    name       VARCHAR(50),
    department VARCHAR(50),
    rate       DECIMAL(10, 2)
);

INSERT INTO employees VALUES
    ('Alice', 'Engineering', 6000.00),
    ('Bob',   'Sales',       4000.00);

INSERT INTO contractors VALUES
    ('Alice', 'Engineering', 6000.00),
    ('Bob',   'Marketing',   5500.00);

Alice appears in both tables with the same name and department, and Bob appears in both with different ones. That is the whole difference between the queries below. If you need the query basics first, start with SQL SELECT.

UNION syntax and rules

The operator goes between two complete SELECT statements, and the pair has to be union compatible: the same number of columns, with compatible data types in matching positions (PostgreSQL 17 documentation). The column names of the result come from the first SELECT, so the names in the second one are ignored.

SELECT name, department FROM employees
UNION
SELECT name, department FROM contractors
ORDER BY name, department;

Four rows went in and three came back. Alice is in both tables with the same department, so her row appears once. Bob is in both with a different department each time, so both of his rows survive: UNION compares the whole row, not the first column.

The ORDER BY matters more than it looks. Without one, nothing decides the order of the result, because appending the second query to the first is only the way the operator is described and not a guarantee about the rows that come back.

UNION vs UNION ALL

UNION ALL is the same operator with the duplicate elimination switched off.

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors
ORDER BY name;

Two employees and two contractors, four rows, and nothing removed. Swap in UNION and the same query answers a different question:

SELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name;

Two rows, because only two names appear across the two tables. Write UNION when a repeated row would be read as two people; write UNION ALL when each row stands for something that really happened, and when you are going to aggregate the result anyway.

How duplicate removal works

The comparison covers every column in the select list. Two rows are duplicates only when they agree in all of them, and null values count as equal in that comparison (PostgreSQL 17 documentation), so two rows that are empty in the same position are still duplicates of each other.

That is why the two queries above disagree about Bob. Selecting name alone puts Bob next to Bob and one of them goes; selecting name, department puts Bob, Sales next to Bob, Marketing, which differ in a column, so both stay. Adding a column to the select list can only increase the number of rows a UNION returns.

ORDER BY and WHERE with UNION

Each branch keeps its own WHERE clause, filtering its own table before the rows are combined:

SELECT name, salary FROM employees WHERE department = 'Engineering'
UNION
SELECT name, rate FROM contractors WHERE rate > 5000
ORDER BY name;

The first branch returns Alice at 6000.00, the second returns Alice at 6000.00 and Bob at 5500.00, and the duplicate Alice goes. The result column is called salary because the first branch named it that, even though half the rows came out of a column called rate.

ORDER BY works the other way round: it belongs to the combined result, so it goes once, at the end. SQL Server states the rule outright, that in a query using UNION, EXCEPT or INTERSECT you can use ORDER BY only at the end of the statement, and that the column names it refers to must be the ones the first query names (SQL Server documentation).

A WHERE that has to see the combined set needs the UNION wrapped in a subquery, where it becomes an ordinary table to select from:

SELECT * FROM (
    SELECT name, salary FROM employees
    UNION ALL
    SELECT name, rate FROM contractors
) AS workers
WHERE salary > 5000
ORDER BY name, salary;

Both Alice rows are here because the inner query is UNION ALL. For more on sorting a combined result, see SQL ORDER BY.

UNION with aggregates and subqueries

A branch can be a grouped query, which is how one report covers two sources of the same measure:

SELECT department, SUM(salary) AS total FROM employees GROUP BY department
UNION ALL
SELECT department, SUM(rate) AS total FROM contractors GROUP BY department
ORDER BY department, total;

Engineering appears twice, once per table, which is rarely what the report wanted. Group the combined set a second time to fold those rows together:

SELECT department, SUM(total) AS combined_total
FROM (
    SELECT department, SUM(salary) AS total FROM employees GROUP BY department
    UNION ALL
    SELECT department, SUM(rate) AS total FROM contractors GROUP BY department
) AS costs
GROUP BY department
ORDER BY department;

UNION ALL is the right operator in both of them. UNION here would remove one of the two Engineering rows before the outer SUM ever saw it, and 12000.00 would come out as 6000.00. For the functions in the inner queries, see SQL COUNT, AVG, and SUM Functions and SQL HAVING Clause.

UNION vs JOIN vs INTERSECT vs EXCEPT

UNION stacks result sets vertically. A join combines them horizontally, matching rows and widening them with the other table's columns:

SELECT e.name, e.department, c.department AS contract_department
FROM employees e
INNER JOIN contractors c ON e.name = c.name
ORDER BY e.name;

Two rows and three columns, against the three rows and two columns the first UNION returned from the same two tables. That difference is the one to hold on to when a query returns the wrong shape.

For the set operator that keeps only what both queries return, continue with SQL INTERSECT Operator.

Common errors and performance tips

Two mistakes account for most of the trouble, and only one of them is caught for you. A branch with a different number of columns is rejected outright, because there is no way to line the columns up:

SELECT name, department FROM employees
UNION
SELECT name FROM contractors;

The same happens when a position holds types that cannot be reconciled, a date against a name. What is not caught is a pair of branches that line up by count and type but not by meaning:

SELECT name, department FROM employees
UNION
SELECT department, name FROM contractors
ORDER BY name, department;

Both columns are text, so the query runs and returns four rows with departments in the name column. Position is what pairs the columns, not the names, and the header of the result comes from the first branch either way.

UNION ALL does nothing beyond producing both result sets. UNION has to eliminate duplicates across them as well. Filter inside each branch rather than around the whole thing, so each WHERE runs against its own table and can use that table's indexes.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Explore UNION queries in DbSchema

A UNION that returns the wrong number of rows is usually one branch's fault, and the way to find out which is to run the branches on their own. Connect DbSchema to the database through its PostgreSQL, MySQL or SQL Server JDBC driver and paste all three statements into the DbSchema SQL Editor: the first branch, the second branch, and the combined query.

Press "Execute Query" to run the statement at the cursor and see that one result as a table, which is how you check a branch's column order and types before it goes into the UNION. Press "Run Script" to run the whole editor content at once; the result sets appear together in the same pane, so the two branch counts and the combined count are on screen side by side.

Reading is all that happens here. Every statement in this article is a SELECT, so nothing changes in the database, and nothing changes in the design model file either.

The row counts decide whether UNION or UNION ALL is the operator you want, and the quickest way to see all three counts is to put them on one screen. Download DbSchema at https://dbschema.com/download.html, connect to your database, and run the two branches and the combined query in the SQL Editor of the free Community Edition.

FAQ

Can ORDER BY be used inside each UNION branch?

PostgreSQL 17 takes an ORDER BY on a single branch only when that branch is wrapped in parentheses. Without them you get a syntax error, or the clause is read as applying to the output of the set operation rather than to one of its inputs (PostgreSQL 17 documentation).

Does UNION treat two rows with NULL in the same column as duplicates?

UNION counts two nulls in the same column as equal values, so that pair of rows collapses into one. The comparison operators go the other way: in PostgreSQL 17 they yield null, signifying unknown, when either input is null, which is why column = NULL matches nothing (PostgreSQL 17 documentation).