UNION vs UNION ALL in SQL: What's the Difference?

Q: What is the difference between UNION and UNION ALL?

Both combine the rows of two or more SELECT statements. UNION ALL returns every row, so the count is the sum of both sides. UNION then removes duplicate rows, comparing every column, across the whole result. Three identical pen | 2 orders come back three times with UNION ALL and once with UNION.

Q: Is UNION ALL faster than UNION?

Yes, because it skips the duplicate check. In SQLite, EXPLAIN QUERY PLAN shows UNION ALL as a plain COMPOUND QUERY that scans one table and then the other, while UNION becomes a MERGE (UNION) with a USE TEMP B-TREE FOR ORDER BY step on each side. Other databases sort or hash the rows instead, but the extra work is the same idea. On small results the gap is tiny; on large ones it can dominate.

Q: Can I use ORDER BY in each SELECT of a UNION?

Not directly. One ORDER BY at the end sorts the whole result; SQLite rejects one placed earlier with ORDER BY clause should come after UNION ALL not before. To sort or limit one side, wrap it in a subquery: SELECT * FROM (SELECT name FROM customers ORDER BY name LIMIT 1) UNION ALL SELECT * FROM (...). The order inside a subquery isn't guaranteed to survive, so sort the outer result if the final order matters.

Q: Does UNION treat NULL values as duplicates?

Yes. For duplicate removal two NULLs count as the same value, so SELECT city FROM customers UNION SELECT city FROM suppliers returns a single NULL row when both tables have a missing city. UNION ALL keeps both. This differs from WHERE city = NULL, which never matches.

Quick answer

Both stack the rows of two SELECTs into one result. UNION ALL keeps every row; UNION then removes duplicate rows from the whole result, including repeats that were already inside one side. Use UNION ALL unless you actually want a distinct list: it does less work and never drops rows you meant to keep.

UNION and UNION ALL append one query's rows to another's, where a join would put columns side by side. Both need the same number of columns on each side, and both write the same way in PostgreSQL, MySQL, SQL Server, Oracle and SQLite. The only difference is the duplicate check, and it matters more than it looks: UNION compares whole rows, so two orders that happen to have the same item and quantity become one. The examples run on SQLite; where another database behaves differently, the note says so. Each one runs on this page: hit Run, then edit the SQL and run it again.

1UNION ALL: keep every rowRecommended

UNION ALL returns the first query's rows followed by the second's, with no filtering, so the row count is always the two counts added together. That makes it the right default for the most common job: combining tables that hold the same kind of data, such as this year's orders and an archive. The last query shows why it is recommended over UNION here: repeated rows are often real data.

union-all.sql

CREATE TABLE orders_2025 (id INTEGER, item TEXT, qty INTEGER);
CREATE TABLE orders_2026 (id INTEGER, item TEXT, qty INTEGER);
INSERT INTO orders_2025 VALUES (1, 'pen', 2), (2, 'ink', 1), (3, 'pen', 2);
INSERT INTO orders_2026 VALUES (4, 'pad', 5), (5, 'pen', 2);

-- Every row from both tables, duplicates included
SELECT item, qty FROM orders_2025
UNION ALL
SELECT item, qty FROM orders_2026;

-- Total quantity per item across both years
SELECT item, SUM(qty) AS total
FROM (
    SELECT item, qty FROM orders_2025
    UNION ALL
    SELECT item, qty FROM orders_2026
)
GROUP BY item
ORDER BY item;

-- The same total with UNION: two of the three pen orders vanish
SELECT item, SUM(qty) AS total
FROM (
    SELECT item, qty FROM orders_2025
    UNION
    SELECT item, qty FROM orders_2026
)
GROUP BY item
ORDER BY item;

Output

UNION ALL returns all five rows, three of them pen | 2, and the totals come out right: pen is 6. With UNION, the three pen | 2 rows look identical once id is left out, so they collapse into one and pen drops to 2, with no error or warning. That is the main reason to reach for UNION ALL first: it returns exactly what the two queries return, and it skips the de-duplication step that UNION has to run over every row. Wrapping the union in a subquery, as here, is how you aggregate, filter or join the combined rows.

2UNION: remove duplicate rows

UNION builds the same combined result and then keeps one copy of each distinct row. The check runs over the whole result, not only between the two sides, so a value repeated inside one table is collapsed too. That is what you want for a list of distinct values, such as every city where you have a customer or a supplier.

union.sql

CREATE TABLE customers (name TEXT, city TEXT);
CREATE TABLE suppliers (name TEXT, city TEXT);
INSERT INTO customers VALUES ('Ana', 'Lima'), ('Ben', 'Oslo'), ('Cy', 'Lima'), ('Dee', NULL);
INSERT INTO suppliers VALUES ('Acme', 'Oslo'), ('Bolt', 'Rome'), ('Core', NULL);

-- Each city once: Lima is twice in customers, Oslo in both tables
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL keeps all seven rows
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- Lima still collapses when the other side has no Lima at all
SELECT city FROM customers
UNION
SELECT city FROM suppliers WHERE city = 'Rome';

-- A duplicate means every column matches: no name/city pair repeats
SELECT COUNT(*) AS pairs FROM (
    SELECT name, city FROM customers
    UNION
    SELECT name, city FROM suppliers
);

Output

The first query returns four rows from seven: NULL, Lima, Oslo and Rome. The two NULL cities also became one, because for duplicate removal NULL counts as equal to NULL, unlike in a WHERE comparison. The third query matches no Lima on the supplier side, yet Lima still appears once: UNION de-duplicates its own input as well. Comparing two columns, all seven name and city pairs differ, so pairs is 7. SQLite happened to return the UNION rows sorted, because it de-duplicates by sorting; that order isn't guaranteed, so add an ORDER BY when it matters. To remove duplicates stored in a table rather than in a query's output, see find and delete duplicate rows.

3UNION vs UNION ALL: which should you use?

Operator	Duplicate rows	Extra work	Use it when
UNION ALL	Kept	None: rows pass straight through	Combining tables whose rows don't overlap, or where repeats are real data (the default)
UNION	Removed from the whole result	Sorts or hashes every row to find repeats	You want a distinct list, such as every city that appears anywhere

4Column rules: count, position and names

Every SELECT in a union must return the same number of columns. They are matched by position, not by name, and the result takes its column names from the first SELECT, so that is where aliases go. When one side has nothing for a column, select NULL (or a literal) in its place. The last statement breaks the count rule on purpose.

columns.sql

CREATE TABLE staff (full_name TEXT, email TEXT);
CREATE TABLE guests (name TEXT, phone TEXT);
INSERT INTO staff VALUES ('Ana Ruiz', '[email protected]'), ('Ben Ode', '[email protected]');
INSERT INTO guests VALUES ('Cy Park', '555-0101');

-- Columns pair up by position; the names come from the first SELECT
SELECT full_name, email FROM staff
UNION ALL
SELECT name, phone FROM guests;

-- Rename them with aliases in the first SELECT
SELECT full_name AS person, email AS contact FROM staff
UNION ALL
SELECT name, phone FROM guests;

-- One side has fewer columns: fill the gap with NULL
SELECT full_name, email FROM staff
UNION ALL
SELECT name, NULL FROM guests;

-- Different column counts are an error
SELECT full_name, email FROM staff
UNION ALL
SELECT name FROM guests;

Output

The guest's phone number lands in the email column in the first result: the database only checks the column count, not what the columns mean, so keep them in the same order on every side. The last statement fails with SELECTs to the left and right of UNION ALL do not have the same number of result columns; PostgreSQL, MySQL and SQL Server reject it too, with their own wording. Types are looser in SQLite than elsewhere: it will put the integer 1 and the text a in one column, while PostgreSQL stops with UNION types integer and text cannot be matched. Cast one side (CAST(n AS TEXT)) to make such a union portable.

5ORDER BY, LIMIT and a source label column

An ORDER BY after the last SELECT sorts the whole combined result; it is not part of that last query. The same goes for LIMIT. To know which table each row came from, add a literal column such as 'customer' AS source to every side. To sort or limit one side on its own, put that side in a subquery.

order-by.sql

CREATE TABLE customers (name TEXT, city TEXT);
CREATE TABLE suppliers (name TEXT, city TEXT);
INSERT INTO customers VALUES ('Ana', 'Lima'), ('Ben', 'Oslo'), ('Cy', 'Lima');
INSERT INTO suppliers VALUES ('Acme', 'Oslo'), ('Bolt', 'Rome');

-- A literal column records where each row came from;
-- ORDER BY goes once, at the end, and sorts the whole result
SELECT 'customer' AS source, name, city FROM customers
UNION ALL
SELECT 'supplier', name, city FROM suppliers
ORDER BY city, name;

-- Sort by position, and LIMIT the combined result
SELECT name, city FROM customers
UNION ALL
SELECT name, city FROM suppliers
ORDER BY 1 DESC
LIMIT 2;

-- The first name per table: ORDER BY/LIMIT inside a subquery
SELECT * FROM (SELECT name FROM customers ORDER BY name LIMIT 1)
UNION ALL
SELECT * FROM (SELECT name FROM suppliers ORDER BY name LIMIT 1);

Output

Sorting by city mixes the two tables (Oslo shows supplier | Acme before customer | Ben), and the source column still tells them apart. A label column also changes what UNION can remove: rows from different tables now always differ, so only repeats within one table collapse. ORDER BY 1 DESC LIMIT 2 keeps Cy and Bolt, the last two names of all five. An ORDER BY placed before UNION ALL is a syntax error in SQLite (ORDER BY clause should come after UNION ALL not before), hence the subqueries in the last query. Sort by the first SELECT's column names, aliases or positions: SQLite also accepts a name that only a later SELECT uses, but PostgreSQL rejects it with column "name" does not exist.

6INTERSECT and EXCEPT

UNION has two siblings with the same column rules. INTERSECT returns the rows that appear in both queries, and EXCEPT the rows of the first query that don't appear in the second. Like UNION, both remove duplicates from their result. Oracle spells EXCEPT as MINUS, and MySQL has had both operators since 8.0.31.

intersect-except.sql

CREATE TABLE customers (name TEXT, city TEXT);
CREATE TABLE suppliers (name TEXT, city TEXT);
INSERT INTO customers VALUES ('Ana', 'Lima'), ('Ben', 'Oslo'), ('Cy', 'Lima');
INSERT INTO suppliers VALUES ('Acme', 'Oslo'), ('Bolt', 'Rome');

-- Cities in both tables
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

-- Customer cities with no supplier (Lima once: EXCEPT de-duplicates too)
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

-- Order matters: supplier cities with no customer
SELECT city FROM suppliers
EXCEPT
SELECT city FROM customers;

Output

Oslo is the only city in both tables, Lima the only customer city without a supplier, and Rome the only supplier city without a customer. When you mix operators, SQLite reads them left to right with equal precedence, while PostgreSQL, MySQL and SQL Server evaluate INTERSECT first: SELECT 1 UNION SELECT 2 INTERSECT SELECT 2 returns only 2 in SQLite and both 1 and 2 in the others. SQLite doesn't accept parentheses around a compound's parts, so group them with a subquery: SELECT 1 UNION SELECT * FROM (SELECT 2 INTERSECT SELECT 2). The ALL forms INTERSECT ALL and EXCEPT ALL, which keep repeats, exist in PostgreSQL and MySQL but not in SQLite or SQL Server. To find rows with no match while also returning other columns, use NOT EXISTS instead, covered in find rows with no match.

Frequently asked questions

What is the difference between UNION and UNION ALL?

Both combine the rows of two or more SELECT statements. UNION ALL returns every row, so the count is the sum of both sides. UNION then removes duplicate rows, comparing every column, across the whole result. Three identical pen | 2 orders come back three times with UNION ALL and once with UNION.

Is UNION ALL faster than UNION?

Yes, because it skips the duplicate check. In SQLite, EXPLAIN QUERY PLAN shows UNION ALL as a plain COMPOUND QUERY that scans one table and then the other, while UNION becomes a MERGE (UNION) with a USE TEMP B-TREE FOR ORDER BY step on each side. Other databases sort or hash the rows instead, but the extra work is the same idea. On small results the gap is tiny; on large ones it can dominate.

Does UNION remove duplicates within the same table?

Yes. The de-duplication runs over the whole combined result, so a value that appears twice in the first query is returned once even if the second query has no match for it. SELECT city FROM customers UNION SELECT city FROM suppliers WHERE city = 'Rome' still returns Lima once although two customers live there.

Can I use ORDER BY in each SELECT of a UNION?

Not directly. One ORDER BY at the end sorts the whole result; SQLite rejects one placed earlier with ORDER BY clause should come after UNION ALL not before. To sort or limit one side, wrap it in a subquery: SELECT * FROM (SELECT name FROM customers ORDER BY name LIMIT 1) UNION ALL SELECT * FROM (...). The order inside a subquery isn't guaranteed to survive, so sort the outer result if the final order matters.

Do the columns in a UNION need the same names and types?

Names no, count yes. Columns are matched by position, and the result uses the first SELECT's names. A different number of columns is an error in every database. Types must be compatible: SQLite accepts an integer and a text value in the same column, but PostgreSQL fails with UNION types integer and text cannot be matched, so cast one side when you mix types.

Does UNION treat NULL values as duplicates?

Yes. For duplicate removal two NULLs count as the same value, so SELECT city FROM customers UNION SELECT city FROM suppliers returns a single NULL row when both tables have a missing city. UNION ALL keeps both. This differs from WHERE city = NULL, which never matches.

Run it yourself

Open any of these in the SQLite playground to tweak, run and share.

SQLite playground