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 and UNION ALL?
• Spot the Difference: Why use UNION over UNION 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.

1. 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, then UNION ALL would be more suitable.
2. 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 and UNION 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 your UNION or UNION ALL query.
• Performance Considerations: If you don’t need distinct rows, always prefer UNION ALL for better performance. Removing duplicates with UNION can be resource-intensive on large datasets.