MySQL UNION 合併查詢結果完整教學：去重與保留重複的差異

UNION 操作子可將多個 SELECT 查詢的結果集合併為單一結果集，要求各查詢的欄位數、順序、資料型態相容。MySQL 4.0+ 才支援。本文以三張結構不同的表（prospect、customer、vendor）示範合併姓名與地址建立郵寄清單。

測試資料

-- prospect 表
+---------+-------+------------------------+
| fname   | lname | addr                   |
+---------+-------+------------------------+
| Peter   | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr.          |
+---------+-------+------------------------+

-- customer 表
+-----------+------------+---------------------+
| last_name | first_name | address             |
+-----------+------------+---------------------+
| Peterson  | Grace      | 16055 Seminole Ave. |
| Smith     | Bernice    | 916 Maple Dr.       |
| Brown     | Walter     | 8602 1st St.        |
+-----------+------------+---------------------+

-- vendor 表
+-------------------+---------------------+
| company           | street              |
+-------------------+---------------------+
| ReddyParts, Inc.  | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+---------------------+

基本 UNION：自動去除重複列

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

重複的「Bernice Smith / 916 Maple Dr.」僅保留一筆。欄位名以第一個 SELECT 為準。

UNION ALL：保留所有列（含重複）

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

效能與使用建議

• UNION 需額外排序去重，效能較 UNION ALL 差；確定無重複或允許重複時優先用 UNION ALL。
• 各 SELECT 的欄位順序與型態必須對應，必要時用 CAST() 或空字串 '' 補齊（如 vendor 表第 2 欄位）。
• 可在最後加 ORDER BY 排序整體結果。