MySQL學習（五） UNION與UNION ALL

UNION用於把來自許多SELECT語句的結果組合到一個結果集合中，也叫聯合查詢。

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

在多個 SELECT 語句中，第一個 SELECT 語句中被使用的欄位名稱將被用於結果的欄位名稱。

當使用 UNION 時，MySQL 會把結果集中重覆的記錄刪掉，而使用 UNION ALL ，MySQL 會把所有的記錄返回，且效率高於 UNION。

數據準備

student表

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `classId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 's1', '20', '1');
INSERT INTO `student` VALUES ('2', 's2', '22', '1');
INSERT INTO `student` VALUES ('3', 's3', '22', '2');
INSERT INTO `student` VALUES ('4', 's4', '25', '2');

teacher表

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', 't1', '36');
INSERT INTO `teacher` VALUES ('2', 't2', '33');
INSERT INTO `teacher` VALUES ('3', 's3', '22');

查詢數據如下

mysql> SELECT * FROM student;
+----+------+-----+---------+
| id | name | age | classId |
+----+------+-----+---------+
|  1 | s1   |  20 |       1 |
|  2 | s2   |  22 |       1 |
|  3 | s3   |  22 |       2 |
|  4 | s4   |  25 |       2 |
+----+------+-----+---------+
4 rows in set

mysql> SELECT * FROM teacher;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | t1   |  36 |
|  2 | t2   |  33 |
|  3 | s3   |  22 |
+----+------+-----+
3 rows in set

使用 UNION的結果

mysql> SELECT id, name, age FROM student
    -> UNION　　-- 與UNION DISTINCT相同
    -> SELECT id, name, age FROM teacher;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | s1   |  20 |
|  2 | s2   |  22 |
|  3 | s3   |  22 |
|  4 | s4   |  25 |
|  1 | t1   |  36 |
|  2 | t2   |  33 |
+----+------+-----+
6 rows in set

使用 UNION ALL的結果

mysql> SELECT id, name, age FROM student
    -> UNION ALL
    -> SELECT id, name, age FROM teacher;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | s1   |  20 |
|  2 | s2   |  22 |
|  3 | s3   |  22 |
|  4 | s4   |  25 |
|  1 | t1   |  36 |
|  2 | t2   |  33 |
|  3 | s3   |  22 |
+----+------+-----+
7 rows in set

其實聯合查詢跟欄位的類型無關，只要求每個SELECT查詢的欄位數一樣，能對應即可，如

mysql> SELECT id, name, age FROM student -- 這裡可以看出第一個SELECT語句中的欄位名稱被用作最後結果的欄位名
    -> UNION
    -> SELECT age, name, id FROM teacher;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | s1   |  20 |
|  2 | s2   |  22 |
|  3 | s3   |  22 |
|  4 | s4   |  25 |
| 36 | t1   |   1 |
| 33 | t2   |   2 |
| 22 | s3   |   3 |
+----+------+-----+
7 rows in set

在聯合查詢中，當使用ORDER BY的時候，需要對SELECT語句添加括弧，並且與LIMIT結合使用才生效，如

mysql> (SELECT classId, id, name, age FROM student WHERE classId = 1 ORDER BY age DESC)
    -> UNION
    -> (SELECT classId, id, name, age FROM student WHERE classId = 2 ORDER BY age);
+---------+----+------+-----+
| classId | id | name | age |
+---------+----+------+-----+
|       1 |  1 | s1   |  20 |
|       1 |  2 | s2   |  22 |
|       2 |  3 | s3   |  22 |
|       2 |  4 | s4   |  25 |
+---------+----+------+-----+
4 rows in set

此時classId為1的學生並沒有按照年齡進行降序，結合LIMIT後

mysql> (SELECT classId, id, name, age FROM student WHERE classId = 1 ORDER BY age DESC LIMIT 2)
    -> UNION
    -> (SELECT classId, id, name, age FROM student WHERE classId = 2 ORDER BY age);
+---------+----+------+-----+
| classId | id | name | age |
+---------+----+------+-----+
|       1 |  2 | s2   |  22 |
|       1 |  1 | s1   |  20 |
|       2 |  3 | s3   |  22 |
|       2 |  4 | s4   |  25 |
+---------+----+------+-----+
4 rows in set