UNION 关键字
如果要从多个表中选择一个接一个的行，或者从一个表中选择多个行的集合作为一个结果集，则可以使用UNION。从MySQL 4.0开始，UNION已可用。本节说明如何使用它。假设您有两个表列出了潜在客户和实际客户，第三个表列出了从中购买供应商的供应商，并且您希望通过合并所有三个表的名称和地址来创建单个邮件列表。UNION提供了一种方法。假设三个表具有以下内容
mysql> SELECT * FROM prospect;
+---------+-------+------------------------+
| fname | lname | addr |
+---------+-------+------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
+---------+-------+------------------------+
mysql> SELECT * FROM customer;
+-----------+------------+---------------------+
| last_name | first_name | address |
+-----------+------------+---------------------+
| Peterson | Grace | 16055 Seminole Ave. |
| Smith | Bernice | 916 Maple Dr. |
| Brown | Walter | 8602 1st St. |
+-----------+------------+---------------------+
mysql> SELECT * FROM vendor;
+-------------------+---------------------+
| company | street |
+-------------------+---------------------+
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+---------------------+
三个表是否都具有不同的列名并不重要。以下查询说明了如何一次从三个表中选择名称和地址
SELECT fname, lname, addr FROM prospect
-> UNION
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname | lname | addr |
+-------------------+----------+------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+-------------------+----------+------------------------+
如果要选择所有记录，包括重复记录，请在第一个UNION关键字后加上ALL
mysql> SELECT fname, lname, addr FROM prospect
-> UNION ALL
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname | lname | addr |
+-------------------+----------+------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Bernice | Smith | 916 Maple Dr. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+-------------------+----------+------------------------+
