What is the difference between Union and Union ALL?

This article will describe the frequently asked interview questions on SQL. After reading this article you will be able to understand the major difference between union and union all.

This is one of the commonly asked interview questions during interviews. One should answer this question since it is a very basic SQL question being asked in an interview.

People often confused when an interviewer asked some scenario-based SQL queries on union and union all. Sometimes we don’t remember the difference between these two when we would have gone through the definitions on these two.

It is always a good idea to prepare with some scenarios on interview questions on SQL so that it will be always there in your mind and never be confused.

Interview questions on SQL: Difference between Union and Union ALL

The Union and Union all these two operators are used to combine the result set of two are more select queries.

We have two tables here TBL India customers and TBL UK customers. Both of the tables have identical columns called ID’s and emails.

Interview questions on SQL

And if I just execute them I get two different SQL statements.

select sql query

I got two rows from both the tables and I get these as two different sets.  How can I combine these two?

Using Union or Union ALL

First, we will see Union ALL. I would like to write a query something like this.

Let us write a query with Union ALL.

Now I got four records including duplicates. The record Sam is duplicated here.

Interview questions on SQL

This SQL query has returned three rows with sorting. We have got one Sam record.

In Union, it is doing two things

  • It is Removing Duplicates
  • It is Sorting the Results

To remove duplicate records it has to perform a distinct sort, which makes it less fast than Union ALL.

Union Operator

Let us see the execution plan of Union statement here.

Union Execution Plan

Look at the above diagram distinct sorting is taking more than 60% of the time .

See the Union ALL Execution Plan here. It runs quickly.

Union ALL Execution Plan

You can also check Top 6 SQL queries asked in interviews here.

I hope this article is useful to you. If you like this article please leave your comments in the comments section.

Would you like to know more about Union and Union ALL?

Check Here

Thank You. Have a good day.

Leave a comment