# What is the difference between UNION and UNION ALL in SQL?

By Bobby Iliev, 2021-11-17. https://bobbyiliev.com/post/what-is-the-difference-between-union-and-union-all-in-sql/

# Introduction 

If you've ever used SQL, you probably know that `UNION`s can be quite confusing at first. In this quick post we are going to learn what the difference between `UNION` and `UNION ALL` is!

# Difference between `UNION` and `UNION ALL`

The main thing to keep in mind is that if you use `UNION` it would remove the duplicate records from the two columns, where as `UNION ALL` does not remove the duplicates.

When using `UNION`, your database server would need to do some extra operations in order to get rid of the duplicate values, which essentially would be slower compared to using `UNION ALL` where the database server would not have to do this extra work. However `UNION` is more commonly used as in most cases you do not need the duplicate records.

# Example

To keep this simple, let's say that we have the following two tables with matching number of columns:

 * Users: with columns `id`, `username`
 * Admins: with columns `id`, `username`

The rows in each table are:

 * Users data:

```
SELECT id,username FROM users;
// Output:
+----+----------+
| id | username |
+----+----------+
|  1 | bobby    |
|  2 | devdojo  |
|  3 | tony     |
|  4 | greisi   |
+----+----------+
```
 
 * Admins data:

```
SELECT id,username FROM admins;
// Output:
+------+----------+
| id   | username |
+------+----------+
|    1 | bobby    |
|    4 | greisi   |
+------+----------+
```

With that out of the way, let's have a couple of examples!

## `UNION` Example

When using `UNION` your database would do the extra work that we mentioned above, and get rid of the duplicate rows, in our case we have `bobby` and `greisi` present in both tables, so if we were to run the following query:

```
SELECT id,username FROM users UNION SELECT * FROM admins;
```

The output that we would get would be the following:

```
// Output
+------+----------+
| id   | username |
+------+----------+
|    1 | bobby    |
|    2 | devdojo  |
|    3 | tony     |
|    4 | greisi   |
+------+----------+
```

As you can see there are no duplicate records.

## `UNION ALL` Example

Unlike `UNION` the `UNION ALL` statement would keep the duplicates.

Let's see what happens when we run the same query as above, but use `UNION ALL` instead:

```
SELECT id,username FROM users UNION ALL SELECT * FROM admins;
```

Output:

```
+------+----------+
| id   | username |
+------+----------+
|    1 | bobby    |
|    2 | devdojo  |
|    3 | tony     |
|    4 | greisi   |
|    1 | bobby    |
|    4 | greisi   |
+------+----------+
```

As you can see in this case, the rows from `admins` table were directly appended after the `users` rows including the duplicated values.

# Conclusion

This is pretty much it! Now you know what the difference between a `UNION` and `UNION ALL` is!

In case that you are just getting started with SQL, I would suggest making sure to check out this free eBook here:

[💡 Introduction to SQL eBook](https://github.com/bobbyiliev/introduction-to-sql)

In case that you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out [Materialize](https://materialize.com/)!

![Materialize - a streaming database](https://imgur.com/52d9a6h.png)

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.