Let’s see one simple example of UNION and UNION ALL, this will not only show you how they work but also where you can use it. This example is from my sample database and following screenshot is from SQL Server Management Studio 2014. We have two tables, Employee and Customer. In order to use UNION and UNION ALL, I have kept same persons as employee and customer, so you will see same id on emp_id and customer_id, and same name as well. If you look at result of first two select queries, you will see that first query returns two rows and second query returns three rows, where two rows have exactly same data as first query. Key things to note is that column names are different in both result set, first one has emp_idand emp_name, while second data set has customer_id and customer_name, but most important both dataset has only two columns. This is must in order to combine them using UNION and UNION ALL keywords. Third query is an example of how to use UNION clause in SQL, you can see that combined result has just three columns, all are unique. Duplicate columns from second result set was not included. This is more like how you do UNION in Set theory, where final result contains data from both set. Fourth query is how you should use UNION ALL, it contains five rows, two from first query and three from second query. It has not removed duplicate rows from second query, that’s why you see Ken and Bob repeating twice. This example teaches us core concept that UNION doesn’t depend upon column name but the data. You can combine result of as many queries as possible until number of columns in all of them is same and data is from same set.
Regarding performance, you need to run UNION and UNION ALL with 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 client.
Difference between UNION and UNION ALL command in SQL
Now we know how union and union all works and has some background by following above examples, let’s summarise the similarities and difference between them for quick revision :
1) Both UNION and UNION ALL are used to combine result of two separate SQL query, it could be on same table or different table but data should be same. E.g. if product_id is used in two table e.g. Product and Order, then two sql queries which pulls product_id from these two table can be combined using UNION or UNION ALL.
2) Key difference between UNION and UNION ALL is that former will remove duplicates but later will keep them. In another words, UNION is equal to running distinct on output of UNION ALL. For example, if product_id 10 is returned by both of SQL query then it will only appear once if you use UNION and appear twice if you use UNION ALL.
3) Due to above difference query execution time of UNION ALL is smaller than UNION, which means former runs faster than later. So if you want faster output and don’t care of duplicates use UNION ALL.
4) 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 appear slower than UNION because it return lot of data with duplicates which require more time to travel from database server to client machine. So evaluate performance of UNION and UNION ALL case by case.
5) Another worth noting thing while using UNION and UNION ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. For example if result of query 1 has three column and result of query 2 has two column then you cannot combine them using UNION command.