MySQL UNION 关键字

• 

  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.  |
  +-------------------+----------+------------------------+

  复制