An email told me that a prestigious organization asked a candidate to choose between UNION and UNION ALL with performance as the top requirement. My first reaction was that this is an apples-and-oranges question: the two operators return different answers when duplicates exist.
Question: Which would you choose for performance, UNION or UNION ALL?
Answer: Choose the required result first. UNION ALL appends every row from both inputs. UNION removes duplicate rows from the combined result. If duplicates are allowed, UNION ALL usually avoids the extra deduplication work. If duplicates must be removed, replacing UNION with UNION ALL just to make a query faster gives the wrong answer.
SELECT 'apple' AS Fruit
UNION
SELECT 'apple'; -- one row
SELECT 'apple' AS Fruit
UNION ALL
SELECT 'apple'; -- two rowsBoth branches need the same number of columns in corresponding positions and compatible data types. UNION stacks rows; it is not a JOIN between columns. Once you know which result is right, inspect the actual plan and workload before claiming a particular elapsed-time difference.
My earlier articles include a performance comparison and why these are different operations. The interview answer is short: semantics first, speed second.
Original supporting examples
- UNION ALL and ORDER BY , How to Order Table Separately While Using UNION ALL
- Introduction and Example of UNION and UNION ALL
- Simple Puzzle Using Union and Union All
- Simple Puzzle Using Union and Union All , Answer
- Insert Multiple Records Using One Insert Statement , Use of UNION ALL
- Union vs. Union All , Which is better for performance?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
If you have option to use both of them, doesn’t that mean that there are no duplicates? Otherwise it’s really not an option to use union all. Also to my mind they are very similar operators and developers don’t understand why / when to use union all.
It should be noted that union and union all can not be used to combine sequence values. Refer this post for more information
That’s a strange non-answer to the q. Given that they did ask the q, the answer is UNION ALL, since it does not require a sort and duplicate removal, as UNION does.
That’s what I said
“You can’t compare their performance as they do an absolutely different task.”
You may also want to look at Kenneth Fisher’s entry in the below URL. Whomever is asking the question may have used this for a reference.
I realize it’s a bit out of context but…
“So what does that mean for you? Unless you actually need to use UNION (Ie you need to get rid of duplicates) then you want to use UNION ALL as it’s the much cheaper and faster option.
There are a couple of exceptions. If you are doing a UNION in an EXISTS clause then SQL knows enough that it doesn’t bother with the sort and the execution times are the same. Also if you are already sorting the output (using an ORDER BY) then most of the cost is already taken care of.”
I agree it’s a bad question but I’d love to know how they expected it to be answered. I frequently have run into interviewers who neither understand the question nor the answer. They just read from a script.
Trick interview question for sure. I gotta hope at least that’s what they’re thinking. Otherwise, they aren’t very knowledgeable in UNION queries.
Correct Paul.