When you need to stack the results of two or more queries into a single result set, SQL gives you two closely related tools: UNION and UNION ALL. They look almost identical, they are often taught in the same lesson, and yet they behave very differently behind the scenes — one quietly removes duplicate rows, the other keeps everything it is given. Consider this: choosing the wrong one can cost you accuracy or performance, sometimes both. This guide breaks down how each operator works, when to use which, and the mistakes that trip up beginners and experienced developers alike Easy to understand, harder to ignore. But it adds up..
What Are UNION and UNION ALL in SQL?
Both operators combine the output of two or more SELECT statements into one result set. The difference lies in how they treat duplicates:
- UNION merges the results and removes duplicate rows, returning only distinct values.
- UNION ALL merges the results and keeps every row, including duplicates.
Think of it like combining two guest lists for an event. UNION gives you a clean list with each name appearing once. UNION ALL gives you the raw stack of both lists, so anyone invited by both hosts appears twice Worth keeping that in mind. That alone is useful..
The Basic Syntax
The syntax for both operators is nearly identical:
SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;
SELECT column1, column2 FROM table_a
UNION ALL
SELECT column1, column2 FROM table_b;
A few structural rules apply to both:
- Each
SELECTmust return the same number of columns. - Corresponding columns must have compatible data types.
- The column names come from the first query; later queries only contribute rows, not headers.
ORDER BYcan only appear once, at the very end, and it sorts the final combined result — not the individual queries.
UNION vs UNION ALL: The Core Difference
Here is the comparison at a glance:
| Feature | UNION | UNION ALL |
|---|---|---|
| Removes duplicates | Yes | No |
| Sorting/dedup overhead | Yes (implicit sort or hash) | None |
| Speed | Slower on large data | Faster |
| Preserves row count | No | Yes |
| Typical use | Clean, distinct lists | Full history, reporting, performance |
The critical insight is that UNION performs extra work. On top of that, on large tables, that step can dominate query time. To eliminate duplicates, the database engine must compare every row against every other row — usually through a sort or a hash operation. UNION ALL simply concatenates the result sets and returns them, which is why it is almost always the faster choice when duplicates are acceptable or impossible.
How UNION Removes Duplicates in Practice
Imagine two tables tracking newsletter subscribers from different signup forms:
SELECT email FROM signup_form_a
UNION
SELECT email FROM signup_form_b;
If alex@example.In practice, com appears in both tables, the result contains that email once. This is exactly what you want when building a master mailing list — sending the same person two copies of a newsletter is a mistake.
UNION treats two rows as duplicates only when every selected column matches. If you select email and signup_date, the same person with two different dates will appear twice, because the rows are not identical Less friction, more output..
How UNION ALL Works in Practice
Now consider an audit log split across monthly tables:
SELECT user_id, action, created_at FROM logs_january
UNION ALL
SELECT user_id, action, created_at FROM logs_february;
Every event is preserved, including repeated actions by the same user. That is the correct behavior for an audit trail — collapsing duplicates would destroy evidence of what actually happened Most people skip this — try not to..
UNION ALL is also the right tool when you already know duplicates cannot exist, such as combining rows from tables with non-overlapping primary keys or adding a summary row to a detail set Surprisingly effective..
Performance: Why UNION ALL Usually Wins
Because UNION must deduplicate, it typically triggers an internal sort or hash aggregate. On a result set of a few million rows, that can mean seconds of extra processing and significant memory or temp-space usage. UNION ALL skips that step entirely.
A practical rule of thumb:
- Default to
UNION ALLunless you specifically need distinct rows. - Use
UNIONwhen duplicate removal is a genuine business requirement. - If you need distinct rows but want control, consider
UNION ALLfollowed by an explicitSELECT DISTINCT— sometimes the optimizer handles it better, and the intent is clearer to readers.
Always check execution plans when performance matters. Optimizers vary across PostgreSQL, MySQL, SQL Server, and Oracle, and what is fast in one engine may not be in another But it adds up..
Rules and Gotchas to Remember
- Column order matters.
SELECT id, nameunioned withSELECT name, idwill not raise an error if the types happen to be compatible — it will silently produce nonsense. Match columns positionally and by meaning. - Data types must be compatible. Mixing an integer and a string column may force an implicit conversion, or fail outright depending on the engine.
ORDER BYapplies to the whole result. To sort each query separately, wrap it in a subquery or use aLIMITinside parentheses where supported.- NULLs are treated as equal for deduplication. Two rows with
NULLin the same column are considered duplicates byUNION. - Column aliases only come from the first query. Aliasing in later queries is ignored for the output header.
UNION vs JOIN: Don't Confuse Them
A frequent beginner mix-up is treating UNION as a substitute for JOIN. They solve different problems:
JOINcombines columns side by side, matching rows across tables horizontally.UNIONstacks rows vertically, appending one result set beneath another.
If you need customer details next to their orders, use a JOIN. If you need customers from two regional databases in one list, use UNION That's the part that actually makes a difference..
Frequently Asked Questions
Does UNION remove duplicates automatically?
Yes. That is its defining behavior. UNION ALL is the one that keeps duplicates But it adds up..
Can I use UNION with more than two queries?
Yes. You can chain as many SELECT statements as you need, mixing UNION and UNION ALL — though mixing them can be confusing, so keep the intent explicit.
Is UNION ALL always faster? In most cases, yes, because it skips deduplication. The gap widens as row counts grow.
Can UNION combine queries from different tables? Absolutely, as long as the column counts and data types line up. The tables do not need any relationship.
Does UNION guarantee sorted output?
No. Some engines happen to return sorted results as a side effect of deduplication, but you should never rely on that. Add an explicit ORDER BY if order matters.
Conclusion
UNION and UNION ALL are simple operators with an important distinction: one cleans duplicates, the other preserves everything. Understanding
Understanding when to apply each variant is essential for both performance and correctness. Which means in practice, the choice hinges on two factors: the necessity of duplicate elimination and the expected size of the result set. If the underlying tables are large and you can tolerate repeated rows, UNION ALL is usually the wiser default because it avoids the overhead of a sort‑based deduplication step. Conversely, when the business logic explicitly requires a unique list — such as a master roster of active users drawn from multiple shards — UNION becomes necessary, even if it incurs a modest additional cost.
Because optimizers differ across RDBMS platforms, it is prudent to examine the execution plan for any query that employs UNION or UNION ALL. Which means in PostgreSQL, for example, the planner may materialize intermediate results before performing the deduplication step, while SQL Server might push the UNION ALL directly into a streamed plan, eliminating the need for a temporary table. On the flip side, mySQL’s optimizer can sometimes rewrite a UNION into a UNION ALL if it can prove that duplicate removal is unnecessary, but this is highly dependent on the presence of ORDER BY clauses and the use of DISTINCT‑compatible predicates. Oracle tends to be the most strict about type coercion, so mismatched columns can cause implicit conversion penalties that negate any performance benefit of using UNION ALL.
A few practical guidelines can help you get the most out of these set operators:
- Align column definitions explicitly – list the columns in the same order and with compatible types in every SELECT. Even if the engine permits implicit casting, it may lead to hidden conversion costs or runtime errors.
- Prefer
UNION ALLunless distinctness is required – the performance gap becomes noticeable as row counts climb into the millions. - Add an explicit
ORDER BYat the outermost level – this guarantees deterministic sorting and prevents reliance on accidental ordering that may differ between engine versions. - Watch for hidden side effects – some systems materialize the union result in a temporary table, which can affect transactional behavior or lock usage; testing with
EXPLAINhelps surface such surprises. - Keep the intent clear – when mixing
UNIONandUNION ALLin a single chain, comment each segment or use parentheses to delineate logical groups, reducing future maintenance overhead.
By adhering to these practices, developers can harness the expressive power of UNION and UNION ALL while minimizing performance surprises across heterogeneous database platforms. Even so, in summary, the operators are straightforward to use but demand careful attention to column alignment, data type consistency, and the presence or absence of deduplication. When leveraged correctly, they provide a clean, set‑oriented way to combine results from disparate queries without the need for complex joins or procedural logic Not complicated — just consistent. No workaround needed..