SQL Union vs Union ALL
Union and Union All are two of the most important functions you will come across in SQL. There are must-to learn and easy to master. So I am going to provide you with a a few examples of using these SQL functions.
Here’s what we’ll cover:
- The Basics: What are
UNION
andUNION ALL
? - Spot the Difference: Why use
UNION
overUNION ALL
(and vice versa)? - Real-life Scenarios: Practical examples to help you grasp when to use which.
- Tips and Tricks: Make the most of these commands without getting tripped up.
What is the Difference between Union and Union All
UNION | UNION ALL : |
Combines the result set of two or more SELECT statements. Removes duplicate rows. Result sets must have the same number of columns, and corresponding columns must have compatible data types. | Combines the result set of two or more SELECT statements. Does not remove duplicate rows. |
Let’s take a look at two tables Student A
| ID | Name |
|----|---------------|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Student B
| ID | Name |
|----|---------------|
| 2 | Bob |
| 4 | David |
| 5 | Eve |
Here are two queries using Union and Union All
SELECT Name FROM Students_A
UNION
SELECT Name FROM Students_B;
The result of this query is |
| Name |
|---------|
| Alice |
| Bob |
| Charlie |
| David |
| Eve |
You can see from this query that Bob is present in both tables, it appears only once in the result because UNION
removes duplicates.
Let’s check out the result of Union All:
SELECT Name FROM Students_A
UNION ALL
SELECT Name FROM Students_B;
| Name |
|---------|
| Alice |
| Bob |
| Charlie |
| Bob |
| David |
| Eve |
With Union ALL we can clearly see the results retain duplications in the form of Bob.
Real-life Scenarios: Practical examples to help you grasp when to use which.
- Online Store Orders: Imagine you’re managing an online store. You have two lists – one of all the orders made in the morning and one from the evening. If you want to see a list of unique products ordered throughout the day, you’d use
UNION
. But if you’re interested in the total number of orders, regardless of duplicates, thenUNION ALL
would be more suitable. - Survey Data: You’ve conducted a survey in two different regions. To get a combined list of unique respondents, you’d use
UNION
. However, if you wanted a full tally of responses, including those who might’ve taken the survey in both regions,UNION ALL
would come to the rescue.
Tips and Tricks: Making the most of these commands without getting tripped up.
- Ensure Matching Columns: Both
UNION
andUNION ALL
require the SELECT statements being combined to have the same number of columns, and the columns must have compatible data types. - Order Matters (sometimes): If you’re using an
ORDER BY
clause, it will typically apply to the entire combined result set. Make sure to place it at the very end of yourUNION
orUNION ALL
query. - Performance Considerations: If you don’t need distinct rows, always prefer
UNION ALL
for better performance. Removing duplicates withUNION
can be resource-intensive on large datasets.