SQL UNION Operator: UNION vs UNION ALL with Examples
For someone learning SQL who needs one list out of two queries; UNION and UNION ALL are run on two small tables in PostgreSQL, MySQL and SQLite.
On this page
Two tables hold the same kind of row, and the report needs one list that covers both. A join would widen each row with the other table's columns; here you want the rows of one query stacked under the rows of another. UNION does that and removes repeated rows. UNION ALL does the same and keeps every row.
SELECT column_a, column_b FROM first_table
UNION
SELECT column_a, column_b FROM second_table
ORDER BY column_a;
Write UNION ALL in place of UNION to keep the repeats. The two queries have to agree:
- They return the same number of columns.
- Columns pair up by position, not by name, so each position needs compatible types.
- The result takes its column names from the first query.
- One
ORDER BY, after the last query, sorts the whole result.
What UNION does with two tables
The examples use two 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 is in both tables with the same department, and Bob is in both with a different one. Every query below ran on PostgreSQL 17.9, MySQL 9.1 and SQLite 3.50, the text says where the engines differ, and the result tables are copied from PostgreSQL. SQLite prints 6000 where the other two print 6000.00.
SELECT name, department FROM employees
UNION
SELECT name, department FROM contractors
ORDER BY name, department;
| name | department |
|---|---|
| Alice | Engineering |
| Bob | Marketing |
| Bob | Sales |
Four rows went in and three came back. Alice's two rows match in every column, so UNION keeps one of them. Bob's rows differ in department, so both stay. If the SELECT itself is new to you, start with SQL SELECT.
UNION vs UNION ALL
UNION ALL skips the duplicate check, so all four rows come back:
SELECT name, department FROM employees
UNION ALL
SELECT name, department FROM contractors
ORDER BY name, department;
| name | department |
|---|---|
| Alice | Engineering |
| Alice | Engineering |
| Bob | Marketing |
| Bob | Sales |
Write UNION when a repeated row would be read as two of the same thing, such as one customer listed twice in a mailing list. Write UNION ALL when each row stands for something that happened separately, such as two payments of the same amount, and whenever you are going to sum or count the result: removing duplicates there changes the total, as the section on totals shows.
UNION ALL is also less work, because the database never compares rows. The plans show the extra step: in PostgreSQL 17's EXPLAIN, UNION puts a HashAggregate on top of the Append that is the whole UNION ALL plan, and MySQL 9.1 adds a step called "Union materialize with deduplication".
Practice this: Lesson 12 of the SQL tutorial runs UNION and UNION ALL on a shop database in your browser, with checked exercises.
Which rows count as duplicates
UNION compares whole rows. Select name alone, and Bob's two rows become duplicates:
SELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name;
| name |
|---|
| Alice |
| Bob |
So every column you add to the select list can keep more rows apart, never fewer.
Two NULLs in the same position count as equal in this comparison, even though NULL = NULL is not true (PostgreSQL 17, SQLite):
SELECT 'Alice' AS name, NULL AS phone
UNION
SELECT 'Alice', NULL;
| name | phone |
|---|---|
| Alice | NULL |
One row, on all three engines. SELECT NULL = NULL on the same engines returns NULL, so a WHERE phone = NULL would match nothing while UNION still treats the two rows as one.
WHERE, ORDER BY and LIMIT with UNION
Each query keeps its own WHERE, which filters 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;
| name | salary |
|---|---|
| Alice | 6000.00 |
| Bob | 5500.00 |
The second query read rate, yet the column is called salary, because the first query named it.
ORDER BY sorts the combined result, so it goes once, after the last query. Put it inside the first query and every engine refuses:
SELECT name FROM employees ORDER BY name
UNION
SELECT name FROM contractors;
- PostgreSQL 17:
ERROR: syntax error at or near "UNION" - MySQL 9.1:
ERROR 1064 (42000): You have an error in your SQL syntax - SQLite 3.50:
ORDER BY clause should come after UNION not before
To sort or limit one query on its own, such as taking the lowest-paid person from each table, wrap it in a subquery, which runs on all three engines:
SELECT * FROM (SELECT name, salary FROM employees ORDER BY salary LIMIT 1) AS e
UNION ALL
SELECT * FROM (SELECT name, rate FROM contractors ORDER BY rate LIMIT 1) AS c
ORDER BY name, salary;
It returns Bob at 4000.00 and Bob at 5500.00. PostgreSQL and MySQL also take the query in plain parentheses (PostgreSQL 17, MySQL); SQLite 3.50 rejects them with near "(": syntax error.
Leave out the final ORDER BY and the order of the rows is not defined (PostgreSQL 17). UNION does not sort either: the name-only query above, run without its ORDER BY, returned Bob before Alice on PostgreSQL and Alice before Bob on MySQL and SQLite. More on sorting in SQL ORDER BY.
Totals across both tables
To total a measure that lives in both tables, combine the per-table totals in a subquery and group the combined rows again:
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;
| department | combined_total |
|---|---|
| Engineering | 12000.00 |
| Marketing | 5500.00 |
| Sales | 4000.00 |
Change the UNION ALL to UNION and Engineering comes out as 6000.00. Both tables produce the row (Engineering, 6000.00), the two rows are duplicates, and one is removed before the outer SUM sees it. Inside a total, write UNION ALL. The functions are covered in SQL COUNT, AVG, and SUM Functions and SQL HAVING Clause.
Errors when the queries don't line up
A different number of columns is rejected everywhere:
SELECT name, department FROM employees
UNION
SELECT name FROM contractors;
- PostgreSQL 17:
ERROR: each UNION query must have the same number of columns - MySQL 9.1:
ERROR 1222 (21000): The used SELECT statements have a different number of columns - SQLite 3.50:
SELECTs to the left and right of UNION do not have the same number of result columns
Types are where the engines part ways. Here a text column is paired with a number:
SELECT name FROM employees
UNION
SELECT rate FROM contractors;
PostgreSQL 17 stops with ERROR: UNION types character varying and numeric cannot be matched (PostgreSQL 17). MySQL 9.1 and SQLite 3.50 run it and return four rows that mix names and amounts in one column, so on those two engines the mistake reaches the report.
Columns that match in count and type but not in meaning get through on all three:
SELECT name, department FROM employees
UNION
SELECT department, name FROM contractors
ORDER BY name, department;
| name | department |
|---|---|
| Alice | Engineering |
| Bob | Sales |
| Engineering | Alice |
| Marketing | Bob |
Both columns are text, so the query runs and puts departments in the name column. Check that the second query lists its columns in the first query's order.
UNION vs JOIN, INTERSECT and EXCEPT
INTERSECT and EXCEPT take the same two queries and compare their rows instead of stacking them:
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;
SELECT name, department FROM employees
EXCEPT
SELECT name, department FROM contractors;
| operator | rows returned | which rows |
|---|---|---|
UNION | 3 | every distinct row of either query |
INTERSECT | 1: Alice, Engineering | the rows that both queries return |
EXCEPT | 1: Bob, Sales | the first query's rows that the second lacks |
A join is the other way to combine two tables. It matches rows on a column such as name and puts the two tables' columns side by side, so its rows get wider rather than more numerous. MySQL has had INTERSECT and EXCEPT since 8.0.31 (MySQL), and Oracle Database 23ai accepts EXCEPT as a synonym of its MINUS (Oracle). The next operator in this series is the SQL INTERSECT Operator.
Check each query of a UNION in DbSchema
When a UNION returns more or fewer rows than you expect, run each query on its own and compare the counts with the combined one. Connect DbSchema to the database through its PostgreSQL, MySQL or SQLite JDBC driver, open the DbSchema SQL Editor, and type the two queries and the combined one, each ending with ;.
Run, or Ctrl+Enter, runs the statement at the cursor and shows its rows in the result grid, which is where you check one query's column order. Run Script, or Ctrl+Shift+Enter, runs the whole editor one statement after the other and prints each result as a block of text, so the three results sit one under the other.
These are SELECT statements, so they read the live database and change nothing in it or in the DbSchema model file.
Download DbSchema, connect it to your database, and run both queries and their UNION in the SQL Editor, which the free Community Edition includes.
FAQ
What happens when UNION and UNION ALL are mixed in one query?
The operators run left to right unless parentheses say otherwise, and a plain UNION removes the duplicates from everything to its left (PostgreSQL 17, MySQL):
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors
UNION
SELECT name FROM employees
ORDER BY name;
This returns two rows, Alice and Bob, because the final UNION deduplicates the four rows that UNION ALL built. Swap the two operators and the query returns four rows: Alice and Bob from the UNION, then both employees again.

