Difference between UNION vs UNION ALL in SQL? Example (original) (raw)

Hello guys, what is the difference between UNION vs UNION ALL is one of the most popular SQL interview questions and often asked programmers during a telephonic round of interviews. Though both UNION and UNION ALL is used to combine results of two SELECT queries, the main difference between them is that UNION doesn't include duplicate record but UNION ALL does. Another difference between them is that UNION ALL is faster than UNION but may look slow because it returns more data which takes more time to travel via the network. The difference between UNION and UNION ALL can be a tricky SQL question, especially for developers, who have not used this useful keyword ever.

Since the UNION clause is not as common as a SELECT clause in SQL, it's usually asked in a telephonic round of programming interviews to check whether the candidate is comfortable with SQL or not. It's in the same league of questions likeclustered vs non-clustered index or primary vs unique key. UNION is very different than other SQL commands because it operates on data rather than columns.

Anyway, the answer to this question is simple, though bothUNION andUNION ALL are used to combine the result of two separate SQL queries on the same or different table, UNION does not keep a duplicate record (a row is considered duplicate if the value of all columns is same), while UNION ALL does.

Since you mostly don't want duplicate rows, UNION is preferred overUNION ALL in reporting and application development. By the way, you should keep in mind thatUNION ALL performance better than UNION because it doesn't have to remove duplicates, so no extra work.

This keyword is very well supported by all major databases likeOracle,Microsoft SQL Server,MySQL, andPostgreSQL. Another thing to keep in mind is the amount of data returned byUNION ALL; if your database server is quite far away and you have limited bandwidth,UNION ALL may appear slower thanUNION because of the number of duplicates it returned.

The cost of transferring duplicate rows can exceed the query execution benefits in many cases. We will see a couple of_examples UNION and UNION ALL in SQL_, but before that few things to keep in mind. In order to combine the results of two queries, they must contain the same number of columns.

For example, if one query contains 3 columns and the other contains 4 columns then you can not useUNION orUNION ALL. This is because a row will only be considered duplicated when all columns will have the same value, irrespective of the name of the columns themselves.

By the way, if you are just starting with SQL and not familiar with essential SQL concepts and commands then I highly recommend you to join an online SLQ course like SQL for Newbs: Data Analysis for Beginners on Udemy to start with. One of the highest-rated beginner SQL courses on Udemy and you can get it for just $10 on sales.

UNION and UNION ALL Example in Microsoft SQL Server

Let's see one simple example ofUNION andUNION ALL, this will not only show you how they work but also where you can use them. This example is from my sample database and the following screenshot is from SQL Server Management Studio.

We have two tables,Employee andCustomer. In order to useUNION andUNION ALL, I have kept the same persons as employee and customer, so you will see the same id onemp_id andcustomer_id, and the same name as well.

If you look at the result of the first twoselect queries, you will see that the first query returns two rows and the second query returns three rows, where two rows have exactly the same data as the first query.

Key things to note is that column names are different in both result sets, first one hasemp_id andemp_name, while second data set has customer_id and customer_name, but most important both dataset has only two columns. This is a must in order to combine them usingUNION andUNION ALL keywords.

The third query is an example of how to use the UNION clause in SQL, you can see that the combined result has just three columns, all areunique. Duplicate columns from the second result set were not included. This is more like how you doUNION in Set theory, where the final result contains data from both sets.

The fourth query is how you should useUNION ALL, it contains five rows, two from the first query and three from the second query. It has not removed duplicate rows from the second query, that's why you seeKen andBob repeating twice.

This example teaches us the core concept that the UNION doesn't depend upon the column name but the data. You can combine the result of as many queries as possible until the number of columns in all of them is the same and the data is from the same set.

Regarding performance, you need to run UNION and UNION ALL with a large database, containing millions of rows. There you can monitor how much time both takes and compare them.

Theoretically, UNION ALL should take less time to execute but more time to transfer data to the client. By the way, if are new to SQL Server then I recommend you to go through an introductory course like Microsoft SQL for Beginners on Udemy. It's a great course to start learning SQL using MSSQL and tools.

Difference between UNION ALL and UNION in SQL SERVER

Difference between UNION and UNION ALL command in SQL

Now we know how union and union all works and has some background by following the above examples, let's summarise the similarities and difference between them for quick revision :

1. Combining Results

BothUNION andUNION ALL are used to combine the results of two separate SQL queries, it could be on the same table or a different table but the data should be the same. For example, ifproduct_id is used in two tables like Product andOrder, then two SQL queries which pullsproduct_id from these two tables can be combined usingUNION orUNION ALL.

2. Duplicates

The key difference betweenUNION andUNION ALL is that the former will remove duplicates but later will keep them. In other words,UNION is equal to runningdistinct on the output ofUNION ALL. For example, if product_id 10 is returned by both SQL query then it will only appear once if you useUNION and appear twice if you useUNION ALL.

3. Execution time

Due to the above difference query execution time ofUNION ALL is smaller thanUNION, which means the former runs faster than the latter. So if you want faster output and don't care about duplicates useUNION ALL.

This is something you can deduce from your existing SQL knowledge and that's where working on fundamentals pays off. If you want to improve your SQL skill or just want to revise SQL concepts then An Introductory Guide to SQL course on Educative, an interactive learning platform is a good place to start with.

best interactive course to learn SQL

And, if you find the Educative platform and their interview courses like Grokking the System design useful then consider getting an Educative Subscription which provides access to their 100+ courses for just $14 per month. It's very cost-effective and great for preparing for coding interviews.

4. Speed and Bandwith Usage

You should keep in mind that benefits gained by not removing duplicates can be easily wiped out by transferring more data over a poor bandwidth network connection. That's why in practice some time_UNION ALL appears slower thanUNION_ because it returns a lot of data with duplicates which require more time to travel from database server to client machine. To evaluate the performance ofUNION andUNION ALL case by case.

5. Number of Columsn on ResultSet

Another worth noting thing while usingUNION andUNION ALL is that all queries combined using aUNION,INTERSECT, orEXCEPT operator must have an equal number of expressions in their target lists. For example, if the result of query 1 has three columns and the result of query 2 has two columns then you cannot combine them using the UNION command.

That's all on the difference between the UNION andUNION ALL command in SQL. It's one of the useful commands to combine the result of twoSELECT queries when they contain the same data. There are many practical scenarios whereUNION is very useful, for example when you need to create a list out of different tables containing data from the same set.

The main difference between UNION and UNION ALL is about duplicates, the former removes it while later keeps it, other differences between them on performance and networking bandwidth usage can be easily derived by knowing this difference. Also keep in mind that it is well supported big three databases like MySQL, Oracle, and SQL Server. Let us know if you have been asked this question in your SQL interview.

Other related SQL queries, Interview questions, and articles:

Thanks for reading this article, if you like this SQL article, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P. S. - If you are a beginner and interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at theIntroduction to Databases and SQL Querying free course on Udemy to kick-start your learning.