UNION combines two result sets and removes duplicate rows, while UNION ALL keeps every row and skips the extra work. Here is how both behave with duplicates and NULLs, how much faster UNION ALL was in my test, and how to fix the common UNION errors, tested in PostgreSQL 16 and SQLite.
UNION stacks the results of two queries and removes duplicate rows. UNION ALL stacks them and keeps every row, duplicates included. Because UNION has to sort or hash the whole result to find duplicates, UNION ALL is faster, about 12 times faster in my 2 million row PostgreSQL test. Use UNION ALL by default, and switch to UNION only when you actually need distinct rows.
Both need the same number of columns in each query, with compatible types, and they take column names from the first query. Those three rules cause most of the UNION errors people search for, so I cover them with the real messages below.
I ran every query on PostgreSQL 16.2 and on SQLite 3.46.1 (through Python's sqlite3 module) with the same sample data. Where the two databases disagree, I show both outputs.
In this guide:
Two small tables of customers, one per year. Ana appears twice in 2025 and again in 2026, and Cy has a NULL country in both years:
CREATE TABLE customers_2025 (id INTEGER, email TEXT, country TEXT);
CREATE TABLE customers_2026 (id INTEGER, email TEXT, country TEXT);
INSERT INTO customers_2025 VALUES
(1, 'ana@example.com', 'UK'),
(2, 'ben@example.com', 'US'),
(3, 'cy@example.com', NULL),
(4, 'ana@example.com', 'UK');
INSERT INTO customers_2026 VALUES
(10, 'ana@example.com', 'UK'),
(11, 'dee@example.com', 'BD'),
(12, 'cy@example.com', NULL);That gives us exact duplicates within a table, duplicates across tables, and duplicate NULL values, which covers every case that trips people up. If you run queries like these from PHP, my guide to PDO prepared statements shows how to pass values into each branch safely.
SELECT email, country FROM customers_2025
UNION
SELECT email, country FROM customers_2026
ORDER BY email;PostgreSQL 16:
email | country
-----------------+---------
ana@example.com | UK
ben@example.com | US
cy@example.com |
dee@example.com | BD
(4 rows)SQLite 3.46:
email | country
ana@example.com | UK
ben@example.com | US
cy@example.com | NULL
dee@example.com | BD
(4 rows)Seven input rows became four. Ana appeared three times and now appears once, and Cy's two rows with a NULL country collapsed into one. Notice that the duplicate inside customers_2025 itself was removed too: UNION removes duplicates from the whole combined result, not just between the two queries. The PostgreSQL documentation on combining queries says it eliminates duplicate rows from its result, in the same way as DISTINCT.
SELECT email, country FROM customers_2025
UNION ALL
SELECT email, country FROM customers_2026
ORDER BY email;PostgreSQL 16:
email | country
-----------------+---------
ana@example.com | UK
ana@example.com | UK
ana@example.com | UK
ben@example.com | US
cy@example.com |
cy@example.com |
dee@example.com | BD
(7 rows)SQLite 3.46:
email | country
ana@example.com | UK
ana@example.com | UK
ana@example.com | UK
ben@example.com | US
cy@example.com | NULL
cy@example.com | NULL
dee@example.com | BD
(7 rows)All seven rows came back. PostgreSQL shows NULL as an empty cell by default, while my SQLite printer writes NULL. The data is the same.
A quick way to see how many rows UNION dropped is to count both versions side by side:
SELECT
(SELECT COUNT(*) FROM customers_2025) AS rows_2025,
(SELECT COUNT(*) FROM customers_2026) AS rows_2026,
(SELECT COUNT(*) FROM (SELECT email, country FROM customers_2025
UNION ALL
SELECT email, country FROM customers_2026) t) AS union_all_rows,
(SELECT COUNT(*) FROM (SELECT email, country FROM customers_2025
UNION
SELECT email, country FROM customers_2026) t) AS union_rows;PostgreSQL 16:
rows_2025 | rows_2026 | union_all_rows | union_rows
-----------+-----------+----------------+------------
4 | 3 | 7 | 4
(1 row)This surprises people who know that NULL = NULL is not true in a WHERE clause. Set operations use a different rule: two rows are duplicates when every column is "not distinct", and two NULL values are not distinct from each other. That is why Cy appeared only once in the UNION output above.
The SQLite SELECT documentation states this directly for compound queries: NULL values are considered equal to other NULL values when finding duplicate rows. PostgreSQL behaved the same way in my test.
A common complaint is that UNION left duplicates in the result. Almost always, one extra column makes the rows different:
SELECT id, email FROM customers_2025
UNION
SELECT id, email FROM customers_2026
ORDER BY email, id;PostgreSQL 16:
id | email
----+-----------------
1 | ana@example.com
4 | ana@example.com
10 | ana@example.com
2 | ben@example.com
3 | cy@example.com
12 | cy@example.com
11 | dee@example.com
(7 rows)Including id made every row unique, so nothing was removed. UNION compares whole rows, not one column. Select only the columns that define a duplicate, or remove the duplicates afterwards with GROUP BY. If the duplicates live inside one table and you want to delete them, my guide to deleting duplicate rows in MySQL shows the safe patterns.
When you combine tables, you often need to know where each row came from. Add a constant column:
SELECT email, '2025' AS source FROM customers_2025
UNION ALL
SELECT email, '2026' AS source FROM customers_2026
ORDER BY email, source;PostgreSQL 16:
email | source
-----------------+--------
ana@example.com | 2025
ana@example.com | 2025
ana@example.com | 2026
ben@example.com | 2025
cy@example.com | 2025
cy@example.com | 2026
dee@example.com | 2026
(7 rows)Use UNION ALL here. With plain UNION, Ana's two 2025 rows would merge into one, and you would lose a real row without noticing. To roll the sources into one line per email in MySQL afterwards, see my guide to GROUP_CONCAT with ORDER BY and SEPARATOR.
UNION ALL plus GROUP BY answers questions like "which customers appear in more than one list":
SELECT email, COUNT(*) AS times_seen
FROM (
SELECT email FROM customers_2025
UNION ALL
SELECT email FROM customers_2026
) AS all_emails
GROUP BY email
ORDER BY times_seen DESC, email;PostgreSQL 16:
email | times_seen
-----------------+------------
ana@example.com | 3
cy@example.com | 2
ben@example.com | 1
dee@example.com | 1
(4 rows)This only works with UNION ALL, because UNION would reduce every count to 1. If you filter on the count, use HAVING times_seen > 1, as my post on WHERE vs HAVING explains.
SELECT email, country FROM customers_2025
UNION
SELECT email FROM customers_2026;PostgreSQL 16:
ERROR: each UNION query must have the same number of columns
LINE 3: SELECT email FROM customers_2026;
^SQLite 3.46:
Error: SELECTs to the left and right of UNION do not have the same number of result columnsThe fix is to give both sides the same columns. If one table does not have a column, select NULL AS country or a default value in its place. Column order matters too, because columns are matched by position, not by name.
Columns at the same position need compatible types:
SELECT id FROM customers_2025
UNION
SELECT email FROM customers_2026;PostgreSQL 16:
ERROR: UNION types integer and text cannot be matched
LINE 3: SELECT email FROM customers_2026;
^SQLite 3.46:
id
1
2
3
4
ana@example.com
cy@example.com
dee@example.com
(7 rows)Here the two databases disagree. PostgreSQL refuses to mix an integer column with a text column. SQLite runs the query and returns ids and emails in one column, because SQLite uses flexible typing, which its datatype documentation describes. The query is still wrong in both: you almost certainly lined up the columns in the wrong order. When you really want to mix them, cast explicitly, for example SELECT id::text in PostgreSQL.
SELECT email AS address FROM customers_2025
UNION
SELECT email AS contact FROM customers_2026
ORDER BY address;PostgreSQL 16:
address
-----------------
ana@example.com
ben@example.com
cy@example.com
dee@example.com
(4 rows)The result column is called address, the alias from the first query, and the contact alias was ignored. ORDER BY must use the first query's names too. Put your aliases on the first SELECT and you avoid confusion.
An ORDER BY sorts the combined result, so it belongs after the last query. Putting it inside one branch fails:
SELECT email FROM customers_2025 ORDER BY email
UNION
SELECT email FROM customers_2026;PostgreSQL 16:
ERROR: syntax error at or near "UNION"
LINE 2: UNION
^SQLite 3.46:
Error: ORDER BY clause should come after UNION not beforeSQLite's message tells you exactly what to do, while PostgreSQL gives a plain syntax error. If you need to sort or limit one branch on its own, for example the top 5 from each table, wrap that branch in parentheses or a subquery. The PostgreSQL SELECT reference shows the allowed placement of ORDER BY and LIMIT.
To measure the cost of removing duplicates, I created two tables of 1 million rows each that overlap by 500,000 rows, then compared the plans:
CREATE TABLE big_a AS SELECT g AS n, md5(g::text) AS h FROM generate_series(1, 1000000) g;
CREATE TABLE big_b AS SELECT g AS n, md5(g::text) AS h FROM generate_series(500001, 1500000) g;
ANALYZE big_a; ANALYZE big_b;The plan for UNION has an extra Sort and Unique step on top of the scans:
QUERY PLAN
-------------------------------------
Unique
-> Sort
Sort Key: big_a.n, big_a.h
-> Append
-> Seq Scan on big_a
-> Seq Scan on big_b
(6 rows)The plan for UNION ALL just appends one table to the other:
QUERY PLAN
-------------------------
Append
-> Seq Scan on big_a
-> Seq Scan on big_b
(3 rows)I then counted the rows of each version three times. UNION returned 1,500,000 rows in 508.434, 494.114 and 443.801 ms. UNION ALL returned 2,000,000 rows in 38.825, 37.094 and 36.731 ms. That is roughly 12 times faster for UNION ALL, even though it returned more rows. These timings come from one machine with default settings and warm caches, so treat the ratio as a guide, not a benchmark. The PostgreSQL guide to EXPLAIN explains how to read these plans on your own data.
The same rules apply to two related operators. INTERSECT returns rows found in both queries, and EXCEPT returns rows in the first query that are not in the second:
SELECT email FROM customers_2025
INTERSECT
SELECT email FROM customers_2026
ORDER BY email;
SELECT email FROM customers_2025
EXCEPT
SELECT email FROM customers_2026
ORDER BY email;PostgreSQL 16:
email
-----------------
ana@example.com
cy@example.com
(2 rows)
email
-----------------
ben@example.com
(1 row)Ana and Cy were customers in both years, and Ben did not come back in 2026. Like UNION, both remove duplicates by default, and PostgreSQL also supports INTERSECT ALL and EXCEPT ALL. Oracle calls EXCEPT MINUS. For matching rows on a key rather than whole rows, a join is usually clearer; see my comparison of INNER, LEFT, RIGHT and FULL OUTER JOIN.
| UNION | UNION ALL | |
|---|---|---|
| Duplicate rows | Removed (across and within each query) | Kept |
| Rows in my sample | 4 | 7 |
| Two rows with NULL in the same column | Treated as duplicates | Both kept |
| Extra plan step in PostgreSQL | Sort + Unique | None (just Append) |
| 1M + 1M rows count, my machine | About 444 to 508 ms | About 37 to 39 ms |
| Same column count and compatible types required | Yes | Yes |
| Use it when | You need distinct rows | You want every row, or you know there are no duplicates |
The same behaviour applies in SQL Server, as Microsoft's UNION reference describes, and in MySQL. I tested only PostgreSQL and SQLite for this post.
UNION combines the results of two queries and removes duplicate rows. UNION ALL combines them and keeps every row. UNION ALL is faster because it skips the duplicate check.
Yes. UNION has to sort or hash the combined rows to remove duplicates. In my PostgreSQL 16 test on 2 million rows, UNION ALL took about 37 ms and UNION about 444 to 508 ms.
Yes. UNION removes duplicates from the entire combined result, so two identical rows from the same query also become one.
Yes. For set operations, two NULLs in the same column count as not distinct, so rows that differ only by being NULL in the same place are merged.
UNION compares entire rows. If any selected column differs, such as an id or a timestamp, the rows are not duplicates. Select only the columns that define a duplicate.
Yes, once, after the last query. It sorts the whole result and uses the column names from the first query. To sort one branch, wrap it in parentheses or a subquery.
Make every SELECT return the same number of columns in the same order. Add NULL AS column_name or a default value where a table lacks a column.
Last checked: 9 November 2026. Figures, settings and guidance can change, so confirm anything important with the sources linked above.
WHERE filters individual rows before GROUP BY, while HAVING filters the groups after it and can use aggregates like SUM() and COUNT(). Here is how they differ, with real results, errors and query plans from MySQL 8.4, PostgreSQL 17 and SQLite 3.46.
INNER JOIN keeps only matching rows, LEFT JOIN keeps every row from the left table, RIGHT JOIN keeps every row from the right table, and FULL OUTER JOIN keeps everything. Here's each one run on the same two tables in MySQL 8.4 and SQLite, with real output.
GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') turns many rows into one comma-separated string per group. Tested examples for ORDER BY, SEPARATOR, DISTINCT, NULLs, the 1024-byte cut-off, top-N and JSON_ARRAYAGG on MySQL 8.4.
Find duplicates with GROUP BY and HAVING, preview them with ROW_NUMBER(), then delete all but one with a self-join. Error 1093, NULLs, case and spaces, and a UNIQUE key.
0%