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