15｜第十五话：基础篇-MySQL之组合查询

组合查询可以将多个SELECT结果拼接在一起作为最终结果输出，比较典型的是将A表和B表各自符合条件的记录组合起来一起返回，形如：

有两种基本情况，可以使用组合查询：

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据

可以看出来，组合查询可以应用在相同的表，也可以应用在不同的表。

本文主要内容如下：

为了方便说明，仍然使用上一篇文章的表结构和数据来作为演示。

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

一、组合查询-UNION

假设需要从t1表中查询出m1<2和m>2的数据，你会如何查询？最简单的方法是使用OR表达式：

mysql> SELECT m1,n1 FROM t1 WHERE m1 < 2 OR m1 > 2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    3 | c    |
+------+------+
2 rows in set (0.01 sec)

除了这种方式，我们还可以使用UNION关键字来将两个查询语句连在一起，就像这样：

mysql> SELECT m1,n1 FROM t1 WHERE m1 < 2 UNION SELECT m1,n1 FROM t1 WHERE m1 > 2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

除了对单表做组合查询，更多的情况下是针对多表的组合查询。

比如从t1表查出m1<2的记录，从t2表中查出m2>2的记录，将两表的记录进行组合：

mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m2, n2 FROM t2 WHERE m2 > 2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

为了看得更加清晰，我们可以使用括号()：

mysql> (SELECT m1, n1 FROM t1 WHERE m1 < 2) UNION (SELECT m2, n2 FROM t2 WHERE m2 > 2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

二、组合查询-UNION ALL

我们来看个查询：

mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

我们发现，t1表里有3条记录，t2表里有3条记录，但使用UNION把它们合并起来后，只有4条记录。因为使用UNION来合并多个查询的记录会默认过滤掉重复的记录。由于t1表和t2表都有(2, b)、(3, c)这两条记录，所以合并后的结果集就把他俩去重了。如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：

mysql> SELECT m1, n1 FROM t1 UNION ALL SELECT m2, n2 FROM t2;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
6 rows in set (0.01 sec)

三、组合查询-结合ORDER BY和LIMIT

组合查询会把各个查询的结果汇总到一块，如果我们想对最终的结果集进行排序或者只保留几行的话，可以在组合查询的语句末尾加上ORDER BY和LIMIT子句，就像这样：

mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2 ORDER BY m1 DESC LIMIT 2;
+------+------+
| m1   | n1   |
+------+------+
|    4 | d    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

这里需要注意的一点是，由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名（别名也可以）。

如果对各个小的查询加入ORDER BY，并不会起到什么作用，这是因为MySQL中规定组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的。

四、总结

可以看到组合查询十分简单，不过我们有一些注意点需要关注，否则容易犯错。

1、组合查询的两张表的内容需要对齐，包括列数需要一致，否则一定会报错，比如第一个select返回m1, n1两个字段，而第二个select只返回n2，此时会报错：

mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT n2 FROM t2 WHERE m2 > 2; 
ERROR 1222 (21000): The used SELECT statements have a different number of columns

2、组合查询应避免出现列的字段内容不对应的情况，以及避免相同位置列的类型不同，虽然MySQL不会报错，但会造成逻辑混乱，毕竟数据已经混乱变得无意义，比如下面这种情况：

mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT n1, m1 FROM t1 WHERE m1 > 2;
+------+------+
| m1   | n1   |
+------+------+
| 1    | a    |
| c    | 3    |
+------+------+
2 rows in set (0.01 sec)

3、这几个查询语句的结果集都可以被合并到一个大的结果集中，但是这个大的结果集总是要有展示一下列名的吧，所以就规定组合查询的结果集中显示的列名将以第一个查询中的列名为准，上边的例子就采用了第一个查询中的m1, n1作为结果集的列名。
4、在组合查询中，ORDER BY应该写在最后，对整体的组合结果做排序，不要放在每个小的查询中，因为并不会有啥效果。每个小的查询中可以使用limit限制查询的条数，这个是没有问题的。
5、使用UNION来合并多个查询的记录会默认过滤掉重复的记录，如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询。