MySQL Unions

Unions are something every MySQL developer (and other SQL developers) should have in their toolbelt. Put simply, unions allow you to combine and sort data from multiple queries. An example of this is if you have a number of different events, in completely separate tables, and you want to show the user a list of recent events, arranged chronologically.

Soviet Union

The symbol of the Soviet Union, who proved just how powerful unions can be.

For this example, I'm going to use two tables containing messages and comments. To get the required data individually you'd have two queries:

SELECT messageId, message, sender, date
FROM messages
ORDER BY date DESC
LIMIT 5

and

SELECT commentId, comment, commenter, date
FROM comments
ORDER BY date DESC
LIMIT 5

Now, this will get us five messages and five comments and we could choose to display comments or messages first. However, what we want is ten of the most recent from both messages and comments, displayed in order of date. Here's how we do it.

(
SELECT "message" AS type, messageId, message, sender, date
FROM messages
)
UNION
(
SELECT "comment", commentId, comment, commenter, date
FROM comments
)
ORDER BY date
LIMIT 10

This will give us the ten required results. You might notice that I've added "message" and "comment" into the queries, this is so that you can easily identify whether it's a message or a comment when you're displaying the results.

There's a couple of important points when dealing with unions, first that the number of fields in each miniquery has to be equal. If you want an extra field for one miniquery, add "" for the other(s). Also, the field names in the result are all taken from the first miniquery. If you have any empty quotes in that query, remember to use aliases (AS fieldname).

 
12/12/2009