Managing & Using MySQL, 2nd Edition

Unions

One of the newest MySQL features as of MySQL 4.0 is the support for SQL unions. A union is simply a tool for combining the results from multiple selects into a single result set listing. A MySQL union looks like this:

SELECT first_name, last_name
FROM Author
UNION
    SELECT fname, lname
    FROM Editor;

This query will provide a list of all authors and editors in the database. The list will include in the first column the values of the first_name column for authors and the values of the fname column for editors. The second column will include the last_name values for authors and lname values for editors.

If one person is an author and an editor, he will appear a single time in the list. You can, however, get MySQL to show the person twice in the results by using the ALL keyword:

SELECT first_name, last_name
FROM Author
UNION ALL
    SELECT fname, lname
    FROM Editor;