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,
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.
Next time you need to combine two tables where the shared data structure is similar, give UNIONs a try!