mysql union all 并行_MySQL——UNION与UNION ALL

UNION用于把来自许多SELECT语句的结果组合到一个结果集合中，也叫联合查询。

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

在多个 SELECT 语句中，第一个 SELECT 语句中被使用的字段名称将被用于结果的字段名称。

当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

数据准备

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;

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

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;

INSERT INTO `teacher` VALUES ('1', 't1', '36'), ('2', 't2', '33'), ('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