Understanding unions in MySQL

A UNION query is used to combine the results of two or more SELECT statements into a single result set. Specifically, it takes the values from one table or query, and instead of putting them side-by-side with another table or another query, it puts them together over-under.

SELECT 1
UNION
SELECT 2;

This will output the result set of 1 and 2, as if they were in a single column.

| 1 |
|---|
| 1 |
| 2 |

Notice how I used the UNION keyword to combine the two results. Also, bear in mind that the number of columns in all SELECT statements must be the same, with the same data types, or else you'll get an error.

Looking at the above query, you'll see that this could be achieved with a simple SELECT statement. UNIONs are best used when we need to combine two queries that would otherwise be difficult to create in one query.

For example, let's say you have two tables, customers and staff, both with similar column names. You want to combine the results of both tables into a single result set, to produce a mailing list.

SELECT
  first_name,
  last_name,
  email_address
FROM customers

UNION ALL

SELECT
  first_name,
  last_name,
  email_address
FROM staff;

Notice that we use UNION ALL instead of UNION to prevent MySQL from eliminating duplicate rows, which can be computationally expensive when we have a large result set. Instead, we simply combine the result sets, knowing that duplicates may exist.

This results in a single list of all staff and customers, with a shared data structure. Besides mailing lists, other applications for unions include when combining archived data with active data or comparing data in different tables that share a similar structure.

Conclusion

Next time you need to combine two tables where the shared data structure is similar, give UNIONs a try!