MySQL Cookbook

Selecting Records in Parallel from Multiple Tables

Problem

You want to select rows one after the other from several tables, or several sets of rows from a single table—all as a single result set.

Solution

Use a UNION operation to combine multiple result sets into one.

Discussion

A join is useful for combining columns from different tables side by side. It’s not so useful when you want a result set that includes a set of rows from several tables one after the other, or different sets of rows from the same table. These are instances of the type of operation for which a UNION is useful. A UNION allows you to run several SELECT statements and concatenate their results “vertically.” You receive the output in a single result set, rather than running multiple queries and receiving multiple result sets.

UNION is available as of MySQL 4.0. This section illustrates how to use it, then describes some workarounds if you have an older version of MySQL.

Suppose you have two tables that list prospective and actual customers, a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. UNION provides a way to do this. Assume the three tables have the following contents:

mysql> SELECT * FROM prospect; +---------+-------+------------------------+ | fname | lname | addr | +---------+-------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | +---------+-------+------------------------+ ...