MySQL UNION 合并结果集

UNION 用于将多个 SELECT 语句的结果合并到结果集中。第一个 SELECT 语句的列名被用作返回结果的列列名。在每个 SELECT 语句的相应位置列出的选定列应该有相同的数据类型。

测试数据和表结构

mysql> desc books;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)        | NO   |     | NULL    |                |
| create_time | datetime            | NO   |     | NULL    |                |
| is_hot      | tinyint(3) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from books;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

组合查询

where 和 union 语句在多数情况下可以实现相同的结果集。where 可以实现的语句，一定可以用 union 实现。而 union 可以实现的语句，where 却不一定能做到。因为，union 是可以针对多张表进行结果集的合并。
查询 id = 1 的记录

mysql> select * from books where id = 1;
+----+-----------------+---------------------+--------+
| id | name            | create_time         | is_hot |
+----+-----------------+---------------------+--------+
|  1 | Head First Java | 2017-12-04 17:19:36 |      0 |
+----+-----------------+---------------------+--------+
1 row in set (0.00 sec)

查询 is_hot = 1 的记录

mysql> select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

使用 union 合并两个查询

mysql> select * from books where id = 1 union select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)

当然，我们通过 select * from books where id = 1 or is_hot = 1 也可以做到，但是 union 是不限于表的。也就是说，我们可以从多个表中查询结果集出来，然后 merge 到第一个查询语句查询的结果集中（前提是字段数量和类型一致）。

重复项

默认情况下，我们单单使用 union 语句，会自动帮我们去除重复的项。但是，如果我们使用了 union all 语句，数据库不会帮我们自动去除重复项。

mysql> select * from books where id > 5 union select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from books where id > 5 union all select * from books where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
+----+---------------------------------------+---------------------+--------+
6 rows in set (0.00 sec)

关于排序

union 中的排序是比较坑的，即使我们在部分结果集这种写了 order by 语句。因为，union 会在合并结果集之后自动的进行排序。意味着在子结果集中的排序，排了也是白排。例如：

mysql> (select * from books where is_hot = 1 order by create_time desc)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc);
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

从查询的意图来看，我们是想让 is_hot = 1 和 is_hot = 0 的结果集各自按 create_time 字段进行排序。但是结果出乎意外，并没有按我们设定的顺序进行。
其实，子结果集是进行了排序的，只不过 union 在合并结果集之后，会自动的对整个结果集进行排序。
假如我们现在有一个需求，is_hot = 1 的是热门书单，is_hot = 0 的是一般书单，然后按热门书单按 create_time 排序，普通书单也按 create_time 排序，返回一个集合。或者分页查询。我们该如何实现这个需求呢？查询语句如下：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 7);
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
|  1 | Head First Java                       | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java                         | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code                            | 2017-12-01 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
8 rows in set (0.00 sec)

在每一个查询语句的后面都写上 limit 7 (总条数)。那么分页呢？

mysql> (select id, name, is_hot, create_time from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select id, name, is_hot, create_time from books where is_hot = 0 order by create_time desc limit 7)
    -> limit 3 offset 0;
+----+---------------------------------------+--------+---------------------+
| id | name                                  | is_hot | create_time         |
+----+---------------------------------------+--------+---------------------+
|  8 | Pro Spring Boot                       |      1 | 2017-12-09 17:19:36 |
|  6 | Java 9 Data Structures and Algorithms |      1 | 2017-12-07 17:19:36 |
|  4 | The Ruby Programming                  |      1 | 2017-12-05 17:19:36 |
+----+---------------------------------------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> (select id, name, is_hot, create_time from books where is_hot = 1 order by create_time desc limit 7)
    -> union
    -> (select id, name, is_hot, create_time from books where is_hot = 0 order by create_time desc limit 7)
    -> limit 3 offset 3;
+----+------------------------------+--------+---------------------+
| id | name                         | is_hot | create_time         |
+----+------------------------------+--------+---------------------+
|  7 | The C Programming Language   |      0 | 2017-12-08 17:19:36 |
|  5 | JavaScript Definitive Guides |      0 | 2017-12-06 17:19:36 |
|  1 | Head First Java              |      0 | 2017-12-04 17:19:36 |
+----+------------------------------+--------+---------------------+
3 rows in set (0.00 sec)

多表查询

创建一张杂志表 magazines 并插入两条数据。

mysql> desc magazines;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)        | NO   |     | NULL    |                |
| create_time | datetime            | NO   |     | NULL    |                |
| is_hot      | tinyint(2) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from magazines;
+----+--------------+---------------------+--------+
| id | name         | create_time         | is_hot |
+----+--------------+---------------------+--------+
|  1 | 男人装        | 2017-12-05 09:28:06 |      0 |
|  2 | 程序员        | 2017-12-01 09:28:06 |      1 |
+----+--------------+---------------------+--------+
3 rows in set (0.00 sec)

假如需求是从两张表或者多张表中查询出结果集，并且分页显示。那么这时候 where 很显然做不到，但是 union 却可以轻松做到。例如：查询 books 和 magazines 表中 is_hot = 1 的数据。

mysql> select * from books where is_hot = 1
    -> union
    -> select * from magazines where is_hot = 1;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  2 | 程序员                                 | 2017-12-01 09:28:06 |      1 |
+----+---------------------------------------+---------------------+--------+
4 rows in set (0.01 sec)

区分多表

联合多张表查询的时候，有时候会需要做到区分数据来自于哪一张表。比如，查询热门杂志和热门书籍，并且分别显示来自于哪一张表。

mysql> select *, 'books' as table_name from books where is_hot = 1
    -> union
    -> select *, 'magazines' as table_name from magazines where is_hot = 1;
+----+---------------------------------------+---------------------+--------+------------+
| id | name                                  | create_time         | is_hot | table_name |
+----+---------------------------------------+---------------------+--------+------------+
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 | books      |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 | books      |
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 | books      |
|  2 | 程序员                                 | 2017-12-01 09:28:06 |      1 | magazines  |
+----+---------------------------------------+---------------------+--------+------------+
4 rows in set (0.00 sec)

这样查询出来的结果集，封装成对象集合返回给前端，也方便前端对 books 或者 magazines 打不同的 tag。

具体应用场景

比如我们要对 books 表中的数据进行分页显示，要求热门书籍在上面，普通书籍在下面。热门书籍按 create_time 倒序，普通书籍也按 create_time 倒序。然后分页，每页显示 5 条数据。
第一页的数据：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 8)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 8)
    -> limit 5 offset 0;
+----+---------------------------------------+---------------------+--------+
| id | name                                  | create_time         | is_hot |
+----+---------------------------------------+---------------------+--------+
|  8 | Pro Spring Boot                       | 2017-12-09 17:19:36 |      1 |
|  6 | Java 9 Data Structures and Algorithms | 2017-12-07 17:19:36 |      1 |
|  4 | The Ruby Programming                  | 2017-12-05 17:19:36 |      1 |
|  7 | The C Programming Language            | 2017-12-08 17:19:36 |      0 |
|  5 | JavaScript Definitive Guides          | 2017-12-06 17:19:36 |      0 |
+----+---------------------------------------+---------------------+--------+
5 rows in set (0.01 sec)

第二页的数据：

mysql> (select * from books where is_hot = 1 order by create_time desc limit 8)
    -> union
    -> (select * from books where is_hot = 0 order by create_time desc limit 8)
    -> limit 5 offset 5;
+----+-----------------+---------------------+--------+
| id | name            | create_time         | is_hot |
+----+-----------------+---------------------+--------+
|  1 | Head First Java | 2017-12-04 17:19:36 |      0 |
|  2 | Think in Java   | 2017-12-02 17:19:36 |      0 |
|  3 | Clean Code      | 2017-12-01 17:19:36 |      0 |
+----+-----------------+---------------------+--------+
3 rows in set (0.00 sec)

参考资料

https://dev.mysql.com/doc/refman/5.7/en/any-in-some-subqueries.html

最后编辑于：2017.12.05 22:37:52

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,968评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,601评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,220评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,416评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,425评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,144评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,432评论 3赞 401
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,088评论 0赞 261
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,586评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,028评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,137评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,783评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,343评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,333评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,559评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,595评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,901评论 2赞 345