Here is what I just learned from email. One of the very prestigious organization asks one of their candidate following questions –
Question: If you have options to use Union or Union All – which one of the option will use keeping performance as a top most requirement for the query.
Answer: Though many of you may be surprised to know that this kind of questions can exist, I am personally not surprised to see this in an interview. Here is my answer – UNION and UNION ALL can’t be compared as they are absolutely different things – they are like apples and oranges. Just like Apples and Oranges are fruits Union and Union All our operators, but they both are there for very different purposes.
UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
You can’t compare their performance as they do an absolutely different task.
Here are articles you can read for further understanding this issue.
Performance comparison: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison
Different between Union and Union All: SQL SERVER – UNION ALL and UNION are Different Operation
Other relevant articles:
- UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
- Difference Between Union vs. Union All – Optimal Performance Comparison
- 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?
- UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
Reference: Pinal Dave (https://blog.sqlauthority.com)
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.