Master UNION ALL SQL interview answers with a one-sentence definition, UNION vs UNION ALL differences, speed, and common follow-up traps.
Knowing the answer and delivering the answer are two different problems. In a union all sql interview question, the gap between "I know what this does" and "I can explain it cleanly under pressure" is exactly where candidates lose points — not because they lack knowledge, but because they haven't rehearsed a precise version of it. This is a playbook for closing that gap: the one-sentence definition, the UNION vs UNION ALL contrast, the performance nuance, and the follow-up traps, in the order you'll actually need them.
Say UNION ALL in One Sentence, Then Move On
The instinct in a technical screening is to over-explain. You get a basic question, you sense it's a test, and you start layering in every edge case you know to prove depth. That instinct backfires on UNION ALL because the interviewer is checking whether you can be precise, not whether you can enumerate SQL set operations from memory.
What a Clean Screening Answer Sounds Like
The one-sentence definition that works in a live screening: "UNION ALL combines the result sets of two or more SELECT statements and returns all rows, including duplicates, without any deduplication step."
That's the answer. Everything after it is defense and elaboration. The candidate who delivers that sentence calmly, then pauses, signals that they know the concept well enough to compress it — which is a stronger signal than someone who immediately pivots into a three-paragraph explanation. Precision reads as confidence. Verbosity reads as uncertainty.
One note from real screening experience: the phrase "without any deduplication step" is the part that tends to get a nod. It tells the interviewer you understand what UNION ALL is not doing, which is often more important than knowing what it is doing. Interviewers who ask this question are usually checking whether you know the contrast with UNION — and landing that contrast in the definition itself means you've already answered the follow-up before they ask it.
What This Looks Like in Practice
The simplest example that makes the duplicate-preserving behavior obvious:
If `customer_id = 101` appears in both `orders_q1` and `orders_q2`, it appears twice in the output. That is not a bug. That is the entire point. If you used `UNION` instead, `101` would appear once, because `UNION` runs a distinct pass over the combined result set before returning rows.
According to PostgreSQL documentation, `UNION ALL` returns all rows from both queries, while `UNION` eliminates duplicate rows from the combined result. This is the authoritative behavior — not a quirk of one database engine, but a defined property of SQL set operations across all major implementations.
UNION ALL vs UNION: Say the Difference Before They Ask Again
The follow-up to any UNION ALL question is almost always a UNION ALL vs UNION comparison. Interviewers ask it so predictably that you should treat it as part of the original question, not a separate challenge.
Why Interviewers Keep Pushing on the Distinction
The distinction isn't syntax trivia. It's a deduplication question dressed up as a SQL question. What the interviewer actually wants to know: do you understand that choosing between these two operators is a semantic decision, not just a performance preference? A candidate who says "UNION ALL is faster" without explaining why and when that matters has given half an answer. The missing half is that UNION ALL is only correct when duplicate rows are acceptable in the output — and that's a business logic question, not a database question.
The interviewers who ask this question repeatedly have seen too many candidates treat UNION ALL as a default because it's faster, without thinking about what duplicate rows mean downstream. That's the gap they're probing.
What This Looks Like in Practice
Take two tables, `sales_north` and `sales_east`, each containing a row with `transaction_id = 5001`:
In a real interview answer, the next sentence after showing this matters: "If these are two genuinely separate transactions that happen to share an ID, UNION ALL is correct. If they're the same transaction appearing in two systems, UNION is correct. The operator doesn't decide — the business logic does."
That framing — putting the decision back in the domain of business logic rather than SQL syntax — is what separates a passable answer from a strong one. It tells the interviewer you think about what queries mean, not just what they do.
Microsoft SQL Server documentation explicitly states that `UNION` performs duplicate elimination while `UNION ALL` does not, and that the result of `UNION` is equivalent to applying `DISTINCT` to the combined output of `UNION ALL`.
Explain the Speed Win Without Overselling It
Why UNION ALL Is Usually Faster Than UNION
The root cause is simple: SQL union all skips the sort-and-dedup pass that UNION requires. When a database engine executes `UNION`, it has to collect all rows from both result sets, sort them (or hash them), and eliminate duplicates before returning anything. `UNION ALL` skips all of that — it concatenates the result sets and returns them immediately.
The cost difference scales with data volume. On small tables, the difference is negligible. On two tables with millions of rows, the deduplication step in `UNION` can add significant I/O and CPU overhead, especially if the result set doesn't fit in memory and the engine has to spill to disk.
What This Looks Like in Practice
Consider combining two large event log tables — `app_events_jan` and `app_events_feb` — each with 50 million rows:
In this scenario, every row in January is by definition distinct from every row in February — they're different time periods. Running `UNION` here would force the database to deduplicate a 100-million-row combined set, even though there are no actual duplicates to remove. That's wasted work. `UNION ALL` is not just faster here; it's the semantically correct choice.
The important caveat for an interview answer: don't oversell the speed difference as the reason to choose `UNION ALL`. The reason to choose it is that duplicates are expected and acceptable. The speed benefit is a consequence of that decision, not the justification for it. Interviewers notice when candidates lead with performance and skip the semantic reasoning — it suggests they picked up the fact without the understanding behind it.
Oracle Database documentation on set operators confirms that `UNION ALL` does not eliminate duplicate rows and therefore does not require the sort operation that `UNION` performs.
Choose UNION ALL When Duplicates Are Part of the Job
When a Smart Candidate Reaches for It
The clearest use cases for a union all query are situations where duplicate rows represent real data, not data quality problems. Monthly extract stacking is the canonical example: you have twelve tables, one per month, each with the same schema, and you need a full-year view. Every row in each monthly table is a legitimate record — combining them with `UNION ALL` is correct because you want all rows from all months, including any that share values across months.
Other legitimate scenarios:
- Event logs from multiple sources — two microservices writing to separate tables, both recording user actions you need to analyze together
- Partitioned table reads — querying multiple partitions explicitly and combining them before aggregation
- Incremental data loads — appending new records to an existing dataset where the new rows may legitimately repeat values from previous loads
In each of these cases, the duplicates aren't noise — they're the data. Using `UNION` would silently discard rows that should be counted.
What This Looks Like in Practice
An analytics pipeline combining two regional sales tables:
If the same product sold on the same date in both regions at the same price, both rows should appear in the output — they represent two separate transactions. A `UNION` here would collapse them into one row and undercount revenue. That's not a hypothetical risk; it's a real bug that has corrupted production reports.
When It Becomes a Bad Habit
`UNION ALL` is safe only when the downstream logic expects and handles duplicates correctly. The failure mode is reaching for it by default — because it's faster, because it's simpler — without checking whether the consumer of that query (an aggregation, a dashboard, a downstream table) can tolerate duplicate rows. If a `COUNT(*)` or `SUM(revenue)` runs on a result set that shouldn't have duplicates but does, the numbers are wrong and there's no error message to tell you so. That's the structural risk worth naming in an interview answer, because it shows you understand the operator's danger, not just its behavior.
Handle Duplicates, ORDER BY, and Recursive CTEs Without Getting Lost
Can UNION ALL Return Duplicate Rows?
Yes, and that's the point. Duplicate rows in SQL produced by `UNION ALL` are not a bug or an edge case — they are the defined behavior of the operator. Every row from the first SELECT and every row from the second SELECT appears in the output, regardless of whether they're identical. If you need to remove duplicates afterward, you can wrap the `UNION ALL` in a subquery and apply `DISTINCT`, or use `UNION` instead. But the operator itself makes no judgment about row uniqueness.
The interview-ready way to say this: "UNION ALL is intentionally non-deduplicating. If you get duplicate rows, that's the operator working correctly. Whether those duplicates are correct for your use case is a separate question."
Where ORDER BY Actually Belongs
A common mistake in written SQL — and in interview answers — is trying to sort each individual SELECT within a UNION ALL:
`ORDER BY` applies to the final combined result set, not to individual SELECT statements within a set operation. The correct placement:
The sort happens after all rows are combined. Trying to sort individual branches is either a syntax error or produces undefined behavior depending on the engine. Knowing this cold — without hesitation — is the kind of detail that distinguishes candidates who have actually written production queries from those who have only read about them.
How This Shows Up in Recursive CTEs
Recursive CTEs are where `UNION ALL` appears in a context that surprises candidates who only know it from basic set operations. The standard recursive CTE pattern requires it:
The `UNION ALL` here is not about combining two independent result sets — it's connecting the anchor member to the recursive member. Using `UNION` instead of `UNION ALL` in a recursive CTE would cause the engine to deduplicate at each recursive step, which breaks the traversal logic and, in some engines, causes an error outright. PostgreSQL's documentation on recursive queries explicitly requires `UNION ALL` (or `UNION`) in the recursive term, and notes that `UNION ALL` is the standard choice for most recursive patterns because deduplication within the recursion is rarely the intended behavior.
If an interviewer asks about recursive CTEs in the context of a UNION ALL question, the answer is: `UNION ALL` is the default in recursive CTEs because you're building a result set iteratively, and deduplication between iterations would corrupt the traversal.
Answer the Follow-Up Traps Before the Interviewer Lands Them
The Mistake Candidates Make When Explaining Deduplication
The most common failure mode in a UNION ALL interview answer is describing it as "just a faster UNION." That framing is technically partially true and strategically wrong. It implies that UNION ALL and UNION are interchangeable modulo performance — that you'd pick UNION ALL when you want speed and UNION when you want correctness. That's backwards. You pick UNION ALL when duplicates are semantically correct, and the speed benefit follows from that decision. Framing it as a performance shortcut invites the follow-up: "So when would you use UNION instead?" — and if you've been thinking about it as a speed dial, you'll fumble that answer.
What This Looks Like in Practice
The follow-up traps interviewers actually use, and the clean responses:
"Do both SELECT statements need the same number of columns?" Yes. UNION ALL requires that each SELECT returns the same number of columns, in the same order, with compatible data types. If column counts differ, it's a syntax error. If data types are incompatible, the engine will either error or attempt implicit casting depending on the implementation.
"What happens if the column names differ between the two SELECTs?" The column names in the output come from the first SELECT statement. The second SELECT's column names are ignored. This is a common gotcha when aliasing columns.
"Can you use UNION ALL with more than two queries?" Yes. You can chain as many SELECT statements as needed. The result is the concatenation of all of them, in order, with no deduplication.
"Where does ORDER BY go in a UNION ALL query?" After the last SELECT, applying to the full combined result. Not inside individual SELECT statements.
How to Sound Confident When They Press for Details
The response pattern that works under follow-up pressure: define it, contrast it, name the risk, give one safe use case. In practice: "UNION ALL returns all rows from both queries including duplicates, unlike UNION which deduplicates. It's faster because it skips that dedup step, but it's only safe when duplicate rows are expected — like combining monthly tables or event logs. The risk is that downstream aggregations get inflated counts if duplicates weren't intended."
That four-part pattern — definition, contrast, risk, safe use case — handles roughly 80% of follow-up questions because it demonstrates you've thought about the operator as a tool with tradeoffs, not just a syntax construct to memorize.
---
Q: What is UNION ALL, and how is it different from UNION in one interview-ready sentence?
`UNION ALL` combines the result sets of two or more SELECT statements and returns all rows including duplicates, while `UNION` performs the same combination but eliminates duplicate rows through a deduplication pass before returning the result.
Q: Why is UNION ALL usually faster than UNION?
`UNION ALL` skips the sort-and-dedup step that `UNION` requires. Because the database doesn't need to collect, sort, and deduplicate the combined rows, it returns results with significantly less CPU and I/O overhead — especially on large result sets where deduplication would require memory or disk spill.
Q: When would a strong candidate choose UNION ALL instead of UNION in a real query?
When duplicate rows are semantically correct — stacking monthly extracts, combining event logs from multiple sources, reading across partitioned tables, or building incremental data loads where repeated values represent distinct real events. The key is that the downstream consumer of the query must expect and correctly handle duplicates.
Q: Can UNION ALL return duplicate rows, and what does that mean for analytics results?
Yes, by design. For analytics, this means any aggregation — `COUNT`, `SUM`, `AVG` — running on a `UNION ALL` result set will include every row, including duplicates. If those duplicates weren't intended, counts and sums will be inflated with no error or warning. This is one of the most common sources of silent data quality bugs in reporting pipelines.
Q: What are the syntax requirements for UNION ALL, including column count and data types?
Each SELECT statement in a `UNION ALL` must return the same number of columns, in the same positional order, with compatible data types. Column names in the output are taken from the first SELECT. If column counts differ, the query errors. If data types are incompatible, behavior depends on the engine — some error, some attempt implicit casting.
Q: How would you explain UNION ALL in the context of a recursive CTE or hierarchy query?
In a recursive CTE, `UNION ALL` connects the anchor member (the base case) to the recursive member (the iterative step). It's the standard choice because you want every row produced at each recursive step to carry forward — deduplication between steps would break the traversal logic and produce incorrect or incomplete hierarchy results.
Q: What common mistake should candidates avoid when discussing UNION ALL in interviews?
Describing `UNION ALL` as simply "faster UNION" without explaining the semantic difference. This framing implies the two operators are interchangeable on a speed dial, which misrepresents the decision. The correct framing: choose `UNION ALL` when duplicates are semantically correct, and the speed benefit is a consequence of that decision — not the reason for it.
---
How Verve AI Can Help You Ace Your Coding Interview With UNION ALL
The part of a technical SQL interview that prep articles can't replicate is the follow-up: the moment the interviewer hears your UNION ALL definition and immediately asks, "OK, so when would you not use it?" That live pivot — from definition to tradeoff to use case — is a performance skill, not a recall skill. Reading the right answer doesn't build it. Saying it out loud, under response pressure, with someone reacting to exactly what you said, does.
Verve AI Coding Copilot is built for that specific gap. It reads your screen during live technical rounds and mock sessions — seeing the query you're writing, the problem statement you're working from, and the follow-up the interviewer just typed — and responds to what's actually happening in the session, not a canned prompt. For SQL questions like UNION ALL, that means Verve AI Coding Copilot can surface the contrast you're about to miss, flag the ORDER BY placement error before you submit it, and prompt you toward the recursive CTE angle if the question is heading there. It works across LeetCode, HackerRank, CodeSignal, and live technical rounds. The Secondary Copilot feature keeps sustained focus on a single problem across a long session — useful when a SQL question opens into a schema design or data modeling thread and you need to hold context without losing your place. Verve AI Coding Copilot suggests answers live and stays invisible while doing it, so the interviewer sees you thinking, not you reading.
---
Conclusion
You walked into this with the basic knowledge and walked out with a structure for delivering it. The screening-room pressure that made UNION ALL feel like a trap — the sense that a "basic" question might have a follow-up you hadn't thought through — has a clean answer: define it in one sentence, contrast it with UNION before they ask, name the performance benefit without overselling it, and identify one real use case and one real risk.
Rehearse the one-sentence definition out loud until it comes out flat and calm, not recited. Then practice one example — the monthly extract or the event log — and one follow-up: "When would you not use it?" If you can answer both of those without pausing to think, you're ready for the question in any form the interviewer chooses to ask it.
Avery Thompson
Interview Guidance
