萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL UNION 聯合查詢完整教學:語法規則、去重邏輯與實戰範例

核心語法

SELECT column_list FROM table1 WHERE condition1
UNION
SELECT column_list FROM table2 WHERE condition2
[ORDER BY column_list];

關鍵規則

  1. 欄位數量相同:每個 SELECT 的選擇列表必須包含相同數量的運算式
  2. 資料型別相容:對應位置的運算式型別需相同或可隱含轉換(數值型會向高精度轉換)
  3. 欄位名稱以第一個 SELECT 為準:結果集的別名取自第一條查詢
  4. 排序只能用第一個 SELECT 的欄位:ORDER BY 放在最後,引用第一條查詢的別名/位置
  5. 預設去重:UNION 等同 UNION DISTINCT,若需保留重複行用 UNION ALL

實戰範例:員工姓名首字母篩選

建表與資料

CREATE TABLE Employee (
    id INT, first_name VARCHAR(15), last_name VARCHAR(15),
    start_date DATE, end_date DATE, salary FLOAT(8,2),
    city VARCHAR(10), description VARCHAR(15)
) ENGINE=InnoDB;

INSERT INTO Employee VALUES
(1,'Jason','Martin','1996-07-25','2006-07-25',1234.56,'Toronto','Programmer'),
(2,'Alison','Mathews','1976-03-21','1986-02-21',6661.78,'Vancouver','Tester'),
(3,'James','Smith','1978-12-12','1990-03-15',6544.78,'Vancouver','Tester'),
(4,'Celia','Rice','1982-10-24','1999-04-21',2344.78,'Vancouver','Manager'),
(5,'Robert','Black','1984-01-15','1998-08-08',2334.78,'Vancouver','Tester'),
(6,'Linda','Green','1987-07-30','1996-01-04',4322.78,'New York','Tester'),
(7,'David','Larry','1990-12-31','1998-02-12',7897.78,'New York','Manager'),
(8,'James','Cat','1996-09-17','2002-04-15',1232.78,'Vancouver','Tester');

UNION 查詢:姓 J 或 R 開頭的員工

SELECT first_name FROM employee WHERE first_name LIKE 'J%'
UNION
SELECT first_name FROM employee WHERE first_name LIKE 'R%';

-- 結果(去重,3 筆)
+------------+
| first_name |
+------------+
| Jason      |
| James      |
| Robert     |
+------------+

對比:WHERE OR 不去重

SELECT first_name FROM employee
WHERE first_name LIKE 'J%' OR first_name LIKE 'R%';

-- 結果(4 筆,James 出現兩次)
+------------+
| first_name |
+------------+
| Jason      |
| James      |
| Robert     |
| James      |
+------------+

UNION ALL:保留重複、效能更好

SELECT first_name FROM employee WHERE first_name LIKE 'J%'
UNION ALL
SELECT first_name FROM employee WHERE first_name LIKE 'R%';

-- 結果(4 筆,含重複 James)
+------------+
| first_name |
+------------+
| Jason      |
| James      |
| Robert     |
| James      |
+------------+

效能提示:UNION ALL 省去排序去重開銷,大數據量下明顯更快。確定業務允許重複或已知無重複時優先使用。

進階:多表聯合與排序

-- 取兩個部門的員工姓名、薪資,按薪資降序
(SELECT first_name, salary FROM employee WHERE city = 'Vancouver')
UNION
(SELECT first_name, salary FROM employee WHERE city = 'New York')
ORDER BY salary DESC;

常見陷阱與解決

問題原因解決
資料型別不匹配錯誤對應欄位型別衝突(如 INT vs VARCHAR)用 CAST() 統一轉型
欄位數不一致SELECT 列表長度不同補齊欄位或調整查詢
ORDER BY 報錯引用了第二個 SELECT 才有的別名只用第一個 SELECT 的別名/位置號
效能差大表 UNION 產生臨時表排序改用 UNION ALL、加索引、分頁
copyright © 萬盛學電腦網 all rights reserved