# 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

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.
• in
• |
• September 14, 2023

Subscribe
Notify of