Interview Question of the Week #017 – Performance Comparison of Union vs Union All

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function
Next Post
SQL SERVER – Collect and Analyze SQL Server Data Efficiently

Related Posts

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.

    Reply
  • It should be noted that union and union all can not be used to combine sequence values. Refer this post for more information

    Reply
  • ScottPletcher
    April 27, 2015 7:53 pm

    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.

    Reply
    • That’s what I said
      “You can’t compare their performance as they do an absolutely different task.”

      Reply
  • twoknightsthenight
    May 3, 2015 5:55 am

    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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu