SQL · Guides · October 30, 2026 · 9 min read

UNION vs UNION ALL in SQL (plus INTERSECT and EXCEPT), with examples

UNION and UNION ALL both stack the results of two queries on top of each other. The only difference: UNION removes duplicate rows from the combined result, UNION ALL keeps every row. Because removing duplicates means extra work, UNION ALL is also faster. The rule of thumb for analysts: use UNION ALL by default, and UNION only when you actually want a list of distinct values. Two related operators complete the family: INTERSECT returns rows found in both queries, and EXCEPT returns rows from the first query that are missing from the second.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output.

One small example for all four operators

The aircrafts table has nine models:

Take two overlapping sets: long-range models (range of at least 5,000 km, five models) and Boeing models (three). Two models, the 777 and the 767, are in both.

UNION: everything, without duplicates

SELECT model FROM aircrafts WHERE range >= 5000
UNION
SELECT model FROM aircrafts WHERE model LIKE 'Boeing%'
ORDER BY model;

Five plus three is eight, but the two Boeings that appear in both sets are kept once: six rows.

UNION ALL: everything, duplicates included

SELECT model FROM aircrafts WHERE range >= 5000
UNION ALL
SELECT model FROM aircrafts WHERE model LIKE 'Boeing%'
ORDER BY model;

Eight rows: UNION ALL just appends the second result to the first.

INTERSECT: only rows in both

SELECT model FROM aircrafts WHERE range >= 5000
INTERSECT
SELECT model FROM aircrafts WHERE model LIKE 'Boeing%'
ORDER BY model;

EXCEPT: rows in the first but not the second

SELECT model FROM aircrafts WHERE range >= 5000
EXCEPT
SELECT model FROM aircrafts WHERE model LIKE 'Boeing%'
ORDER BY model;

Unlike the other three, EXCEPT depends on the order. Swap the queries and you get the Boeings that are not long-range: just the Boeing 737-300. Oracle calls this operator MINUS; PostgreSQL, DuckDB, SQL Server and MySQL 8.0.31+ use EXCEPT.

UNION removes duplicates inside each query too

A common misunderstanding is that UNION only removes rows that appear in both halves. In fact it removes every duplicate in the final result, including repeats within one half. Destinations from Sheremetyevo (SVO) and from Pulkovo in St Petersburg (LED):

• SVO flies to 48 distinct airports, LED to 24, and 8 of them are served from both.
• UNION of the two arrival_airport lists returns 64 rows: 48 + 24 − 8.
• UNION ALL of the same two queries returns 4,881 rows, one per flight, because each destination appears once for every departure in the schedule.

SELECT arrival_airport FROM flights WHERE departure_airport = 'SVO'
INTERSECT
SELECT arrival_airport FROM flights WHERE departure_airport = 'LED'
ORDER BY 1;

So UNION behaves like UNION ALL followed by SELECT DISTINCT. INTERSECT and EXCEPT also return distinct rows by default.

Where UNION ALL is the right answer

Turning two columns into one before counting

Each flight has a departure airport and an arrival airport. To count all movements (take-offs plus landings) per airport, stack both columns into one and then group:

SELECT airport, COUNT(*) AS movements
FROM (
  SELECT departure_airport AS airport FROM flights
  UNION ALL
  SELECT arrival_airport FROM flights
) m
GROUP BY airport
ORDER BY movements DESC
LIMIT 5;

Now replace UNION ALL with UNION and run it again:

Every airport now has exactly one “movement”. UNION collapsed thousands of identical rows into one per airport before COUNT ever saw them. The query runs without an error and the numbers look plausible at a glance, which is what makes this mistake dangerous. Whenever you stack rows to count or sum them afterwards, you want UNION ALL.

A summary table from several sources

UNION ALL is the quick way to put numbers from different tables into one result:

SELECT 'airports' AS metric, COUNT(*) AS value FROM airports
UNION ALL
SELECT 'flights',  COUNT(*) FROM flights
UNION ALL
SELECT 'bookings', COUNT(*) FROM bookings
UNION ALL
SELECT 'tickets',  COUNT(*) FROM tickets;

The label column is what keeps rows apart: even if two tables had the same count, the rows would differ and survive a UNION too. Without a final ORDER BY, though, the database does not promise to return the parts in the order you wrote them. If the order matters, add a sort column.

Adding a total row

A breakdown with a grand total underneath, in one result:

SELECT fare_conditions, COUNT(*) AS segments, ROUND(SUM(amount)) AS revenue
FROM ticket_flights
GROUP BY fare_conditions
UNION ALL
SELECT 'Total', COUNT(*), ROUND(SUM(amount))
FROM ticket_flights
ORDER BY segments;

For more complex subtotals, GROUP BY ROLLUP does the same without repeating the query.

Where EXCEPT and INTERSECT help

EXCEPT is a compact way to ask “what is missing?”, which comes up constantly in data checks. Aircraft models that never appear in the schedule:

SELECT aircraft_code FROM aircrafts
EXCEPT
SELECT aircraft_code FROM flights;

That is the Airbus A320-200: the airline has it in the fleet table but no flights use it. The same pattern answers questions like “loyalty members who never bought a ticket” (11,426 of the 53,000 here) or “customers in last month’s file but not this month’s”.

EXCEPT compares whole rows, so it works on several columns at once. To check whether every route has a return flight, compare (from, to) pairs with (to, from) pairs: SELECT departure_airport, arrival_airport FROM flights EXCEPT SELECT arrival_airport, departure_airport FROM flights. In this schedule it returns no rows, so every route is flown in both directions.

The same questions can be written with NOT EXISTS or LEFT JOIN ... IS NULL (see the anti join section of the joins guide). EXCEPT is shorter when you only need the key columns. When you need other columns of the missing rows, NOT EXISTS is more flexible. One difference to remember: set operators treat two NULLs as equal, while a join condition a.x = b.x never matches NULLs.

The rules for combining queries

Every set operator requires the same shape on both sides:

1. The same number of columns. Otherwise the query fails. DuckDB says: Set operations can only apply to expressions with the same number of result columns.
2. Compatible types, column by column. PostgreSQL refuses SELECT range FROM aircrafts UNION SELECT model FROM aircrafts with “UNION types integer and character varying cannot be matched”. DuckDB is more lenient: it quietly converts the numbers to text and returns a mixed column (5700, 5600, Boeing 777-300…). Do not rely on that; cast explicitly so the query works everywhere.
3. Columns are matched by position, not by name. If the first query selects departure_airport, arrival_airport and the second arrival_airport, departure_airport, they are lined up in that order, which is exactly what the return-route check above relies on, and exactly what silently breaks a report when done by accident.
4. Column names come from the first query. SELECT 'x' AS a UNION ALL SELECT 'y' AS b returns one column called a.
5. ORDER BY and LIMIT at the end apply to the whole result. To sort or limit one part, put it in parentheses:

(SELECT model, range FROM aircrafts ORDER BY range DESC LIMIT 2)
UNION ALL
(SELECT model, range FROM aircrafts ORDER BY range LIMIT 2);

The two longest-range and two shortest-range models in one result.

When you mix operators, INTERSECT binds tighter than UNION and EXCEPT in PostgreSQL and standard SQL, so a UNION b INTERSECT c means a UNION (b INTERSECT c). Use parentheses rather than remembering this.

INTERSECT ALL and EXCEPT ALL

PostgreSQL and DuckDB also support ALL versions of INTERSECT and EXCEPT, which count duplicates instead of removing them. With a left side of 1, 1, 2:

• EXCEPT ALL against 1 returns 1, 2: one of the two 1s is cancelled out.
• INTERSECT ALL against 1, 1, 1 returns 1, 1: as many as the smaller side has.

They are useful for reconciling two tables row by row, for example comparing a source extract with the loaded copy when duplicates are legitimate. MySQL and SQL Server support fewer of these variants, so check before you port a query.

UNION vs JOIN

Both combine two tables, but in different directions. UNION adds rows: the result is as wide as each query and as long as both together. JOIN adds columns: rows from two tables are matched on a key and placed side by side. If you want flights with their aircraft model next to them, that is a join; if you want departures and arrivals in one list, that is a union. Joins are covered in detail in SQL joins explained.

FAQ

What is the difference between UNION and UNION ALL?

UNION returns the combined rows of both queries with duplicates removed. UNION ALL returns all rows, duplicates included. UNION ALL is faster because it does not need to compare rows.

Is UNION ALL faster than UNION?

Yes. To remove duplicates, UNION has to sort or hash the whole result, which costs time and memory on large tables. UNION ALL simply appends. If you know the two parts cannot overlap (for example, they have different labels or come from different date ranges), UNION ALL gives the same rows for less work.

Does UNION remove duplicates within a single table?

Yes. UNION removes duplicates across the whole combined result, including rows repeated inside one of the queries. It works like UNION ALL followed by DISTINCT.

Can I use ORDER BY with UNION?

Yes, once, at the end; it sorts the combined result and can refer to column names from the first query or to column positions. To sort one part before combining, wrap that part in parentheses with its own ORDER BY and LIMIT.

What is the difference between EXCEPT and NOT IN?

Both find rows from one set missing in another. EXCEPT compares whole rows and treats NULLs as equal. NOT IN compares one column and returns nothing at all if the subquery contains a NULL, a classic trap. NOT EXISTS is the safest general choice; EXCEPT is the shortest when you only need the keys.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文