After reading this, you will be able to combine the output of two or more SELECT statements into a single result set, and you will know exactly why UNION and UNION ALL sometimes return the same rows and sometimes return very different ones. You will also know which one to reach for when duplicates matter and which one to avoid when they do not.
Most tutorials introduce UNION as a way to “stack two queries on top of each other,” which is true as far as it goes but leaves out the part that trips people up in practice: the duplicate removal. That one behavior is the source of most confusion, most performance surprises, and most bugs that come from stacking queries together. So instead of walking through the syntax first, this tutorial is organized as a set of myths paired with what commonly happens when you apply them to real data.
How UNION and UNION ALL Differ From JOINs
Before getting into myths, one clarification worth stating plainly, because these two topics get mixed up often.
A JOIN combines columns from two tables side by side, matching rows on a shared key. A UNION combines rows from two queries by stacking them vertically, one result set on top of the other. JOIN adds width to your result. UNION adds height. If you have ever heard someone say “join these two queries together” when they meant stacking, this is the distinction they were reaching for.
If you are not yet comfortable with how JOINs match rows on a key, it is worth reading a JOIN tutorial first, because UNION assumes you already understand how a single SELECT produces a result set — it just takes two of those and merges them.
Myth #1: UNION and UNION ALL Are Interchangeable
This is the most damaging assumption you can carry into a UNION query.
The myth: UNION and UNION ALL both combine two result sets, and the ALL version is just a stylistic preference or a legacy keyword.
The reality: They differ in one behavior that changes your output, and that behavior is duplicate handling.
- UNION stacks the two result sets and then removes duplicate rows, returning only distinct rows across the combined output.
- UNION ALL stacks the two result sets and keeps everything, duplicates included.
That single difference has two consequences. First, the rows you get back can differ. Second, the way the database executes the query differs — UNION typically has to do extra work to deduplicate the combined result, while UNION ALL does not.
A small illustration makes the difference concrete:
-- Left query returns: (1), (2), (3)
-- Right query returns: (3), (4), (5)
SELECT n FROM left_numbers
UNION
SELECT n FROM right_numbers;
-- Result: 1, 2, 3, 4, 5 (the duplicate 3 appears once)
SELECT n FROM left_numbers
UNION ALL
SELECT n FROM right_numbers;
-- Result: 1, 2, 3, 3, 4, 5 (the duplicate 3 appears twice)
If your two queries happen to share no duplicate rows, UNION and UNION ALL produce identical output. That is exactly why the myth survives: in small test queries, the two often look the same. The difference surfaces only when duplicates exist, and by then a query has already shipped.
Myth #2: UNION ALL Is Just the “Faster UNION”
The myth: UNION ALL is a performance setting you enable once you are confident there are no duplicates.
The reality: UNION ALL is not a tuning flag. It is a different operation with a different output guarantee.
When you write UNION, the database has to guarantee distinct rows in the combined result. Depending on the database engine and the shape of the queries, that commonly involves sorting the combined rows, or hashing them, to identify and collapse duplicates. Both approaches cost CPU, memory, and often disk. On large result sets, that overhead is often the dominant cost of the query.
When you write UNION ALL, the database simply concatenates the two streams. No sort, no hash, no deduplication pass.
The practical consequence: if you already know that the two result sets cannot overlap, or if you do not care whether a row appears twice, using UNION means paying for deduplication you do not need. In many workloads, that difference is noticeable on result sets of a few hundred thousand rows or more, though the exact impact depends heavily on the engine, the columns involved, and whether indexes are usable.
The rule of thumb that emerges: default to UNION ALL, and switch to UNION only when you specifically need distinct rows across the combined output.
Myth #3: UNION Removes Duplicates From Each Query Separately
The myth: UNION deduplicates within each of the two queries before combining them.
The reality: UNION deduplicates the combined result, not each input.
This matters because it changes what you will see in the output. Suppose the left query returns two identical rows on its own, and the right query returns completely different rows. UNION will collapse those two identical left rows into one, because the deduplication runs after the two result sets are stacked.
In most situations this is what you want, but it is worth knowing it happens. If the left query returns duplicates you intended to keep — for example, because two separate events produced identical-looking log rows — UNION will silently collapse them. If you need the count of events per row to be preserved, UNION ALL is the correct choice.
A common real-world pattern that runs into this: merging a “current orders” table and an “archived orders” table, where the same order ID might legitimately appear once in each. With UNION ALL, both appearances survive; with UNION, the pair collapses to a single row only if every selected column matches. Since archived and current rows often differ in a timestamp column, the deduplication may have no effect at all — another reason to check columns carefully before assuming UNION is doing what you expect.
Myth #4: The Two Queries Can Have Any Number of Columns
The myth: Since UNION stacks queries, each query can have whatever columns it needs.
The reality: The two queries must return the same number of columns, in the same order, with compatible data types in each position.
The column names come from the first query; the second query’s column names are ignored for the purposes of the combined result. What matters is that position 1 in the left query and position 1 in the right query hold compatible types.
That compatibility is where most UNION errors come from. Common failure modes:
- Mismatched column count. The database raises an error like “each UNION query must have the same number of columns.” Easy to spot, easy to fix.
- Incompatible types in the same position. A date column in position 2 of the left query and a text column in position 2 of the right query may or may not raise an error depending on the engine. Some engines will attempt an implicit cast and produce surprising output; others will refuse. Do not rely on implicit casting — align the types yourself.
- Misaligned column meaning. This one does not raise an error. If the left query selects
(order_id, customer_name, total)and the right query selects(order_id, total, customer_name), the database will happily combine them and produce rows where customer names sit in the total column. This is the most dangerous UNION bug because nothing warns you.
The defense against misalignment is discipline: give every column an explicit alias in every query, and read through the SELECT lists side by side before running the query. A one-minute review prevents a silent data corruption.
Myth #5: ORDER BY Goes Inside Each Query
The myth: If you want each query’s result sorted, you put ORDER BY in each one.
The reality: An ORDER BY inside a UNION’d query is only allowed in specific cases, and it does not control the order of the combined result.
The combined result of a UNION is not sorted by default, and any ORDER BY that applies to the final output has to appear once, at the very end, after the last SELECT. It applies to the whole combined result.
The basic shape looks like this:
SELECT customer_id, 'active' AS status
FROM active_customers
UNION ALL
SELECT customer_id, 'archived' AS status
FROM archived_customers
ORDER BY customer_id;
If you try to put an ORDER BY inside the first query, most databases will reject the query. If the engine allows it (usually via a parenthesized subquery), the ORDER BY in that subquery applies only to that subquery’s own output, and the outer result is still unsorted unless you add a final ORDER BY.
An important companion rule: the ORDER BY at the end can reference columns by their output name (the alias from the first query) or by position number. Referencing columns by position works but is fragile; use aliases instead.
A Concrete Implementation Path: Merging Two Systems’ Customer Lists
Here is a small end-to-end example that walks through setup, change, and verification — the three steps that will cover most UNION work you do.
Setup. Suppose you are consolidating customer records from two systems that were merged after an acquisition. One system uses lowercase status values; the other uses a different casing convention. You want one report listing every customer across both, with a flag showing which system they came from.
-- Table legacy_crm.customers
-- customer_id INTEGER, email TEXT, status TEXT
-- Table new_crm.customers
-- customer_id INTEGER, email TEXT, status TEXT
Change. Write a UNION ALL that selects the same three columns from each table, adds a literal string as a fourth column to identify the source, and orders the final result by email.
SELECT
customer_id,
LOWER(email) AS email,
status,
'legacy' AS source_system
FROM legacy_crm.customers
UNION ALL
SELECT
customer_id,
LOWER(email) AS email,
status,
'new' AS source_system
FROM new_crm.customers
ORDER BY email;
Two details to notice. First, the LOWER(email) normalization is applied inside both queries so that the same email in different casing produces matching output rows — useful when you plan to do a downstream comparison. Second, the source_system literal column is required to appear in both queries, otherwise the columns misalign and the query fails or produces nonsense.
Verify. Run a count of the combined output and compare it against the sum of the two source tables’ row counts:
SELECT COUNT(*) FROM (
SELECT customer_id FROM legacy_crm.customers
UNION ALL
SELECT customer_id FROM new_crm.customers
) combined;
For UNION ALL, that count should equal (SELECT COUNT(*) FROM legacy_crm.customers) + (SELECT COUNT(*) FROM new_crm.customers). If it does not, the two queries are not returning what you think they are — most often because one of them has a WHERE clause that filters more rows than expected.
Now switch to UNION and run the same verification again. The count will typically drop, and the drop tells you how many duplicate customers exist across the two systems. That number is often the interesting output of the whole exercise.
When UNION Is the Wrong Tool
UNION is not a general-purpose “combine things” operator. There are cases where reaching for it creates more problems than it solves.
When a JOIN would express the relationship more clearly. If the rows you want to combine have a shared key and you want the columns side by side, JOIN is the tool. UNION throws away the concept of matching — it just stacks. A query that UNIONs two tables that share a foreign key is usually a sign that the business question was framed wrong.
When you need to preserve row-level duplication. UNION silently removes duplicate rows. If your data model relies on duplicated rows to represent distinct events, UNION destroys that information. Use UNION ALL.
When the two queries have very different shapes. If one query aggregates (returns a summary row) and the other returns per-row detail, stacking them produces a result set where some rows have values in aggregate columns and others have NULLs or missing data. That is sometimes what you want, but it is rarely what you want without a deliberate design. The output is confusing to downstream consumers, and column meanings shift between rows.
When result-set size is a concern. UNION has to buffer the combined result to deduplicate it. On very large result sets, that buffering can consume significant memory or spill to disk. UNION ALL streams and does not buffer for deduplication.
When ORDER BY at the end is expensive. A final ORDER BY on a UNION result forces the database to sort the entire combined output. If the two queries already produce rows in the order you need, consider whether the ORDER BY is necessary at all.
Practical Guidelines to Carry Forward
A few habits worth adopting when you write UNION queries:
- Default to UNION ALL. Switch to UNION only when distinct rows are required, and note in a comment why.
- Alias every column in the first query. The aliases become the column names of the combined result.
- Match column order visually before running. Read the SELECT lists side by side. Silent misalignment is the most expensive UNION mistake because it does not raise an error.
- Normalize types and casing explicitly. If the two queries pull data from sources with different conventions, apply the same transformation in both.
- Put ORDER BY once, at the end, and reference aliases rather than positions.
- Verify counts after the query runs. A quick
COUNT(*)against the combined result versus the sum of sources catches most mistakes early.
Wrapping Up
The core of UNION and UNION ALL fits in a single sentence: they stack two result sets vertically, and the only meaningful difference between them is whether duplicates are removed. Most of the complexity you will encounter when using them comes from forgetting that one difference and being surprised by the consequences — extra rows, missing rows, or unexpected cost.
If you remember one habit from all of the above, make it this: reach for UNION ALL first, and only move to UNION once you have a specific reason to want distinct rows across the combined result.
What are you trying to combine — two tables that overlap, two queries that filter the same source differently, or something else? Tell me the shape of your two queries and I can help you decide whether UNION, UNION ALL, or a JOIN is the right fit.