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

Introduction

If you've ever used SQL, you probably know that UNIONs 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:

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!

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.