UNION and UNION ALL both stack two result sets on top of each other, and both require the same number of columns with compatible types in the same order — that part is identical. The difference is one word: UNION runs an implicit DISTINCT over the combined rows afterward; UNION ALL doesn't. That single word decides whether two genuinely different real-world events that happen to produce an identical row get counted twice, or silently collapsed into one.
Verified by actually running this against SQLite — not hand-computed. A coffee shop logs sales from two channels into separate tables: online_orders and store_orders. Walk-in customers who don't give a name are all logged simply as 'Walk-in' — a common, deliberate choice, not a data quality problem.
CREATE TABLE online_orders(customer_name TEXT, item TEXT, amount REAL);
CREATE TABLE store_orders(customer_name TEXT, item TEXT, amount REAL);
INSERT INTO online_orders VALUES
('Priya','Coffee',150),
('Walk-in','Coffee',150),
('Rahul','Sandwich',220);
INSERT INTO store_orders VALUES
('Walk-in','Coffee',150),
('Meera','Tea',90),
('Walk-in','Coffee',150);online_orders:
| customer_name | item | amount |
|---|---|---|
| Priya | Coffee | 150 |
| Walk-in | Coffee | 150 |
| Rahul | Sandwich | 220 |
store_orders:
| customer_name | item | amount |
|---|---|---|
| Walk-in | Coffee | 150 |
| Meera | Tea | 90 |
| Walk-in | Coffee | 150 |
Six rows total, six real sales — three different walk-in customers on three separate occasions each bought one coffee for ₹150. Nothing here is a mistake or a duplicate entry; it's just three ordinary transactions that happen to look the same on paper because "Walk-in" carries no identifying detail.
A report needs "every order from both channels, combined." Reaching for UNION to stack two same-shaped result sets looks like the obvious tool:
SELECT customer_name, item, amount FROM online_orders
UNION
SELECT customer_name, item, amount FROM store_orders;| customer_name | item | amount |
|---|---|---|
| Meera | Tea | 90 |
| Priya | Coffee | 150 |
| Rahul | Sandwich | 220 |
| Walk-in | Coffee | 150 |
Six real sales became four rows. SELECT SUM(amount) over this combined result reports ₹610 — but the two tables together actually sold ₹910 worth of coffee, tea, and sandwiches. ₹300 of real revenue, two entire transactions, vanished without a single error, warning, or row count that looked obviously wrong.
UNION deduplicates on the entire row, not on any notion of "the same customer" or "the same person." SQL has no way to know that the 'Walk-in','Coffee',150 row from the website and the two identical rows from the till represent three different people who happen to have left the same three-column fingerprint. As far as UNION's implicit DISTINCT is concerned, three identical-looking rows are one row that showed up three times — and it keeps exactly one.
SELECT customer_name, item, amount FROM online_orders
UNION ALL
SELECT customer_name, item, amount FROM store_orders;| customer_name | item | amount |
|---|---|---|
| Priya | Coffee | 150 |
| Walk-in | Coffee | 150 |
| Rahul | Sandwich | 220 |
| Walk-in | Coffee | 150 |
| Meera | Tea | 90 |
| Walk-in | Coffee | 150 |
UNION ALL just concatenates both result sets — no dedup pass, no rows compared against each other, nothing silently merged. All six rows survive, in the order they came from each source. SUM(amount) over this correctly reports the full ₹910.
None of this makes UNION wrong to use — it makes it wrong for combining transactions, where every row is supposed to represent a distinct event even if its columns coincidentally match another row's. UNION earns its keep when a duplicate row genuinely does mean "the same real-world thing, seen twice": merging a newsletter list and a support-ticket list into one set of email addresses to contact, for instance, where the same address appearing in both really should collapse to one entry, not two. The deciding question isn't "do these two tables have the same shape" — both queries answer that the same way — it's "if two rows come out identical, does that mean one real thing or two."
| Operator | Duplicate full rows | Right for |
|---|---|---|
UNION | Collapsed to one | Combining sets of the same real-world entity — e.g. a deduplicated contact list |
UNION ALL | All kept | Combining events, transactions, or logs — anything where each row is a distinct occurrence |
This failure mode is easy to miss precisely because it looks like good hygiene — nobody sets out to write a query that drops real sales, they write one that "removes duplicates," which sounds like exactly the right thing to do until a coincidence in the data turns two real events into one. Before reaching for UNION over UNION ALL, it's worth asking directly: if two rows here happen to be identical, is that actually the same thing twice, or just two different things that ran out of columns to tell them apart?