SELECT column_list FROM table1 WHERE condition1
UNION
SELECT column_list FROM table2 WHERE condition2
[ORDER BY column_list];
SELECT 的選擇列表必須包含相同數量的運算式ORDER BY 放在最後,引用第一條查詢的別名/位置UNION 等同 UNION DISTINCT,若需保留重複行用 UNION ALLCREATE 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');
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 |
+------------+
SELECT first_name FROM employee
WHERE first_name LIKE 'J%' OR first_name LIKE 'R%';
-- 結果(4 筆,James 出現兩次)
+------------+
| first_name |
+------------+
| Jason |
| James |
| Robert |
| James |
+------------+
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、加索引、分頁 |