Ian Klosowicz
UNION and UNION ALL both stack the rows of two queries on top of each other, but they handle duplicates differently. UNION ALL returns every row from both queries, including exact duplicates, and runs faster. UNION returns the same rows with duplicate rows removed, which forces the database to do extra work to compare and dedupe. For most analyst work you want UNION ALL, because it's faster and it keeps every row you asked for. Reach for UNION only when you specifically need a deduplicated result.
That is the whole answer. The rest of this post covers when each one is the right call, why the speed gap exists, the rules both operators share, and the mistakes that quietly corrupt your row counts.
The difference is duplicate handling. UNION ALL keeps every row. UNION removes duplicate rows from the combined result.
Say you have 2 tables of transactions, one for January and one for February, and a customer made the same 20 dollar purchase in both months. If you run UNION ALL, both rows show up, because they really are 2 separate events. If you run UNION and those rows are identical across every column, you get 1 row back, because UNION treats the second one as a duplicate and drops it.
Here is what that looks like in practice:
The word "ALL" is the clue. It means "give me all the rows, don't filter any out." Without it, the database assumes you want a distinct result and silently removes repeats.
Use UNION ALL when you want to keep every row, which in analyst work is most of the time. It is the right default for stacking data that belongs together but lives in separate tables or separate queries.
Common cases where UNION ALL is correct:
I lean on this constantly. In data engineering today I work in Snowflake building pipelines, and when I union staged tables into a model, it's almost always UNION ALL. Deduplication happens later, on purpose, with logic I control. I don't want the set operator deciding which rows disappear.
Use UNION when you want one clean list of distinct values and you're fine paying for the deduplication. The classic case is building a combined list of unique entities from more than one source.
A few examples where UNION earns its keep:
The thing to watch is that UNION dedupes across the entire row, not one column. Two rows count as duplicates only when every selected column matches. If you add a column that differs between the two rows, like a source label or a timestamp, UNION sees them as distinct and keeps both. That surprises people who expected a column-level dedupe and got a full-row one.
Yes. UNION ALL is faster because it skips the deduplication step entirely.
When you run UNION, the database has to find and remove duplicate rows, and the usual way it does that is by sorting or hashing the full combined result so it can spot repeats. On a few hundred rows you'll never notice. On tens of millions of rows, that sort or hash can add real time and chew through memory, and on a large enough result it can spill to disk and slow the whole query down.
The practical rule:
A quiet performance trap: some analysts default to UNION out of habit because it feels safer. On big tables that habit is a tax you pay on every run. If you're curious whether it's costing you, read the query plan. A UNION will show a sort or aggregate step that a UNION ALL doesn't, and that step is the extra cost in plain sight. Learning to read a query plan is one of the skills we drill inside Analyst Hive, because it's what separates someone who writes SQL that works from someone who writes SQL that scales.
UNION and UNION ALL follow the same structural rules. Break one and the query fails or returns something you did not expect.
Column count must match. Both SELECT statements have to return the same number of columns. If the first query returns 3 columns and the second returns 4, the database throws an error. There is no partial stacking.
Data types must line up by position. The operator matches columns by their order, not their names. The first column of query one stacks onto the first column of query two, and so on. If column 2 is a date in one query and a number in the other, you get a type error or an unwanted conversion. Line them up deliberately.
Column names come from the first query. Whatever you name the columns in the first SELECT is what the final result uses. The names in the second query are ignored for output. If you want clean headers, set them in the first query with aliases.
ORDER BY goes at the very end. You sort the combined result once, after the last query, not inside each SELECT. Putting ORDER BY inside the first query is a common beginner error that either fails or gets ignored.
My first data job was in marketing analytics, running Google Sheets and BigQuery with heavy SQL and zero Python. I stacked tables with these operators constantly, and the error that bit me most was a column order mismatch that did not throw an error. The types happened to be compatible, so the query ran and silently put the wrong values under the wrong headers. Nothing warns you when that happens. You only catch it by checking a few rows against the source.
If you need deduplication but want more control than UNION gives you, stack with UNION ALL and dedupe explicitly. This is the pattern I reach for most, because it makes the dedupe visible in the query instead of hiding it inside a set operator.
Two reliable ways to do it:
The reason to prefer the explicit version on large or important queries is control. With UNION, the dedupe is invisible and applies to every column. With a GROUP BY or a DISTINCT you wrote yourself, you decide which columns define a duplicate, you can keep a source column for debugging, and the next person reading the query can see what you did.
Most UNION bugs are not syntax errors. They are quiet logic errors that pass review and corrupt a number downstream. These are the ones I see most from the people who message me about their SQL.
The fix for almost all of these is the same habit: default to UNION ALL, and add deduplication as a step you can see when you actually need it.
Does UNION ALL remove any duplicates at all?
No. UNION ALL returns every row from both queries with no filtering. If the same row appears in both result sets, you get it twice. Removing duplicates is the one job UNION ALL doesn't do, which is exactly why it runs faster than UNION on large result sets.
Why would anyone use UNION instead of UNION ALL?
Because sometimes you want one clean, distinct list and you're fine paying for the dedupe. Building a unique list of customer emails from two sources is a good fit. On small result sets the speed difference is invisible, so the convenience of built-in deduplication wins.
Do the column names have to match between the two queries?
No. The operator matches columns by position, not by name. The final result takes its column names from the first query, so the names in the second query are ignored for output. What does have to line up is the number of columns and their data types, matched in order.
Can you use UNION on more than two queries?
Yes. You can chain as many as you need, stacking query after query with UNION or UNION ALL between each pair. Every query still has to return the same number of columns with compatible types. You can also mix them, though mixing is rare and usually a sign the logic needs a rethink.
Where does ORDER BY go in a UNION query?
At the very end, after the last SELECT. It sorts the entire combined result once. Putting ORDER BY inside one of the individual queries either errors or gets ignored, depending on the database. Sort the final output, not the pieces.
Is UNION ALL faster than UNION in every database?
As a rule, yes. The dedupe step UNION performs costs time and memory in every major database, including Snowflake, BigQuery, Postgres, and SQL Server. The gap is tiny on small data and large on big data, so the bigger your tables, the more UNION ALL saves you.
Knowing when each set operator matters is a small piece of writing SQL that holds up at work. The harder part is building the judgment to spot these calls fast, under a deadline, on tables you did not design. That is the gap most self-taught analysts hit, and it's what Analyst Hive is built to close: a 90-day daily-task program for aspiring data analysts who want to write SQL that holds up on real data at work.
