MySQL學習（五） UNION與UNION ALL

不能差不多發表於2018-07-17
MySql

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

 

相關文章