Have you been working with SQL Server for a while, but aren’t really sure what the difference is between UNION and UNION ALL?
When I was first learning T-SQL, I wondered this myself. I eventually figured it out, and I was glad to see the difference is very easy to understand.
In this very brief tutorial, we’re going to talk about UNION vs UNION ALL.
I have a full tutorial on the UNION set operator if you need a full rundown on what this set operator does and how to use it. I also talk about UNION ALL in that tutorial. Here’s the link:
The UNION Set Operator: Everything you need to know
If you want to learn about all the set operators (UNION, UNION ALL, INTERSECT, EXCEPT), you should dowload the following FREE GUIDE:
Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
The guide discusses the difference between UNION and UNION ALL, as well as all the other set operators in SQL Server. It will definitely be a great resource for you to reference during your career as a database professional. Make sure you get it today!
Let’s get on with the tutorial.
What’s the difference between UNION and UNION ALL?
When using UNION to combine the results of two or more queries into a single result set, UNION will remove duplicate rows from the final result set, while UNION ALL will not remove duplicate rows.
Let’s think of an example. You are a small business owner with two stores, one in Denver Colorado and another in the neighboring town of Boulder Colorado. You collect customer information for customers at each location, as seen here:
Notice there is a customer who exists in both the BoulderBranch table and the DenverBranch table.
It’s Emily Foxtrot.
Emily lives in Boulder, but works in Denver, so she actually shops at both stores depending on which is more convenient at the time. Both locations have collected her first and last name.
So what if we wanted to see the first and last names for all customers we have in our database, for both locations? Well, we can do that with a very simple UNION:
Notice Emily is seen only once. We know she exists in both tables, but she is seen only once.
UNION assumes you don’t want to see the same information twice (or more).
But maybe we do want to see her twice. In that case, we should use UNION ALL:
We’re seeing her twice: Once from the BoulderBranch table, then again from the DenverBranch table.
In situations where you want to keep duplicates, you should use UNION ALL.
Let’s also think about something else: If you know, for sure, the data between your tables does not contain duplicates, you should choose to use UNION ALL as your set operator. This is because UNION will actively look for and try to remove duplicates from your final result set, which we know would be a waste of time if there simply are no duplicates.
It would be like looking through your book collection for your second copy of “The Hobbit“, but knowing you own only a single copy. Why would you do that?
Don’t look for something you know you don’t have, and don’t force SQL Server to look for duplicates that aren’t there!
Next steps:
Leave a comment if you found this tutorial helpful!
In this very brief tutorial, I hope I have answered your questions about UNION vs UNION ALL.
There is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including set operators. The book actually has an entire chapter dedicated to the topic of set operators. You won’t regret owning this book, trust me. Definitely get it today!
If you found this tutorial helpful, don’t forget to…
Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
Again, this tutorial assumes you know what the UNION set operator is and the rules for using it. If you need the full rundown, check out my full tutorial:
The UNION Set Operator: Everything you need to know
And as I said, the other two set operators available to us are INTERSECT and EXCEPT. These set operators are discussed in the full beginner-friendly tutorial on all the set operators. Check it out:
SQL Server Set Operators: The Ultimate Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment! Or better yet, send me an email!
