SQL practice › SQL reference › UNION vs UNION ALL

SQL UNION vs UNION ALL, with queries you can run

UNION and UNION ALL both stack the rows of two queries into one result. UNION ALL keeps every row. UNION then removes the duplicate rows, the way DISTINCT does. So SELECT customer FROM online_orders UNION SELECT customer FROM store_orders lists each customer once, and the same query with UNION ALL returns one row per order.

Pick the wrong one and nothing fails: you get a list with repeats, or a total that is too low. Both are below.

Skip to the 4 exercises ↓

Syntax

SELECT customer, amount FROM online_orders
UNION ALL
SELECT customer, amount FROM store_orders
ORDER BY amount DESC
SELECT customer, amount FROM online_orders
The first query. Its column names become the column names of the result.
UNION ALL
Appends the rows of the next query. Write UNION alone to also remove duplicate rows.
SELECT customer, amount FROM store_orders
The second query. It must return the same number of columns, in the same order.
ORDER BY amount DESC
Goes once, at the very end, and sorts the whole stacked result.

The two tables

A shop keeps its online orders and its in-store orders in two tables with the same columns. Ben has bought in both places, 120 each time. Ava has ordered online twice, 35 each time.

online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Live queryUNION ALL: every row of both queries
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Result · 7 rows
customeramount
Ava35
Ben120
Chloe240
Ava35
Ben120
Dan60
Eve180
Live queryUNION: duplicate rows removed
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Result · 5 rows
customeramount
Ava35
Ben120
Chloe240
Dan60
Eve180

The total that comes out too low

Those two missing rows were real orders. If the stacked rows feed a SUM or a COUNT, UNION changes the answer without any warning.

Live queryRevenue with UNION: 155 short
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Result · 1 row
revenue
635

Both queries need the same number of columns

Rows can only be stacked when they have the same shape. If the two queries return a different number of columns, the query doesn't run.

Live queryTwo columns on top, one below
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run

SQLite says: SELECTs to the left and right of UNION ALL do not have the same number of result columns

The columns are matched by position, not by name: the first column of one query goes above the first column of the other. PostgreSQL and SQL Server also require the two columns in each position to have compatible data types.

Column names come from the first query, ORDER BY sorts everything

The result has one set of column names, and it takes them from the first query. An ORDER BY goes once, after the last query, and applies to the whole result.

Live queryNames from the first query, one ORDER BY at the end
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Result · 7 rows
buyeramountchannel
Chloe240online
Eve180store
Ben120online
Ben120store
Dan60store
Ava35online
Ava35online

UNION or JOIN?

They combine tables in different directions. UNION stacks rows: the result gets longer, with the same columns. JOIN puts columns side by side: each row of the result is made of one row from each table that matched on a condition.

Live queryThe same two tables with a JOIN
Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Result · 1 row
customeronline_amountstore_amount
Ben120120

INTERSECT and EXCEPT

Two more operators are written the same way and follow the same rules about columns. INTERSECT returns the rows that both queries return. EXCEPT returns the rows of the first query that the second doesn't return, so the order of the two queries matters. Both remove duplicate rows, like UNION.

SQLite, PostgreSQL and SQL Server have both. MySQL has them since version 8.0.31. Oracle Database 19c's manual lists MINUS in the place of EXCEPT. The last exercise below can be solved with one of them.

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1One list of all customers

Return the customer names from both tables as one list, each name once.

Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders
Result · 5 rows
customer
Ava
Ben
Chloe
Dan
Eve

Here removing duplicates is exactly what's wanted: Ava ordered online twice and Ben is in both tables, and each should be listed once. UNION does it in one step.

Exercise 2Total revenue of both channels

Return one number: the shop's total revenue, online and in-store orders together.

Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Show the answer
SELECT SUM(amount)
FROM (
  SELECT amount FROM online_orders
  UNION ALL
  SELECT amount FROM store_orders
) AS all_orders
Result · 1 row
SUM(amount)
790

Stack the amounts of both tables with UNION ALL, then add them up in an outer query. With UNION, equal amounts would be counted once. Adding two separate sums, (SELECT SUM(amount) FROM online_orders) + (SELECT SUM(amount) FROM store_orders), gives the same number.

Exercise 3Every order, labelled and sorted

List every order of both tables: the customer, the amount and a third column that says online or store. Biggest amount first; for equal amounts, by customer name from A to Z, then online before store.

Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer, amount, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, amount, 'store' FROM store_orders
ORDER BY amount DESC, customer, channel
Result · 7 rows
customeramountchannel
Chloe240online
Eve180store
Ben120online
Ben120store
Dan60store
Ava35online
Ava35online

Each query adds a constant as its third column, UNION ALL keeps all seven orders, and the single ORDER BY at the end sorts the combined result using the column names of the first query.

Exercise 4Online customers who never bought in the store

Return the customers who have ordered online but never in the store, each name once.

Tables: online_orders, store_orders
online_orders
idcustomeramount
1Ava35
2Ben120
3Chloe240
4Ava35
store_orders
idcustomeramount
1Ben120
2Dan60
3Eve180
Ctrl/⌘ + Enter to run
Show the answer
SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders
Result · 2 rows
customer
Ava
Chloe

EXCEPT returns the rows of the first query that the second doesn't return, without duplicates: Ava and Chloe. Swap the two queries and you get the store-only customers instead. SELECT DISTINCT customer … WHERE customer NOT IN (SELECT customer FROM store_orders) is another way to write it.

Questions people ask

When should I use UNION ALL instead of UNION?

Whenever every row counts: totals, counts, logs, lists of orders or events. Also when the two queries can't return the same row anyway. Use UNION when you want each distinct row once, such as one list of customers from two tables.

What is the difference between UNION and JOIN?

UNION stacks the rows of two queries that return the same columns: the result is longer. JOIN matches the rows of two tables on a condition and puts their columns side by side: the result is wider. The last live query above runs a join on the same two tables that were stacked before.

What is the difference between UNION and INTERSECT?

UNION returns the rows that are in either query. INTERSECT returns only the rows that are in both. EXCEPT returns the rows of the first query that are not in the second. All three remove duplicate rows.

Do the columns need the same names?

No. They need the same number of columns, matched by position. The result takes its column names from the first query: SQLite does it in the live query above, and MySQL's manual states the same rule.

Does UNION sort the result?

Don't count on it. PostgreSQL's manual says there is no guarantee about the order in which the combined rows come back. If the order matters, write an ORDER BY at the end. In SQLite only the last query of the stack may have one, and it sorts everything; PostgreSQL and MySQL let you sort or limit a single query of the stack if you put that query in parentheses.

Are two NULLs duplicates for UNION?

In SQLite, yes: its manual says that when a compound query looks for duplicate rows, NULL counts as equal to NULL. That's the opposite of =, where NULL equals nothing. The page on NULL covers the comparisons.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL JOIN: INNER and LEFT, with live queriesSQL subquery vs CTE: the same question three waysSQL NULL: IS NULL, COALESCE and the NOT IN trapSQL COUNT(DISTINCT), with live queriesSQL interview questions, with answersSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: PostgreSQL documentation: Combining Queries (UNION, INTERSECT, EXCEPT), PostgreSQL documentation: SELECT (UNION clause), SQLite documentation: SELECT (compound select statements, ORDER BY), MySQL 8.4 Reference Manual: Set Operations with UNION, INTERSECT, and EXCEPT, MySQL 8.0 Reference Manual: INTERSECT Clause, MySQL 8.0 Reference Manual: EXCEPT Clause, SQL Server documentation: UNION (Transact-SQL), SQL Server documentation: EXCEPT and INTERSECT (Transact-SQL), Oracle Database 19c SQL Language Reference: The UNION [ALL], INTERSECT, MINUS Operators. Last checked November 2026.