MySQL表组合查询：UNION、UNION ALL

原创

董哥的黑板报 2021-08-28 10:40:55 博主文章分类：MySQL ©著作权

文章标签 合并查询结果组合查询 union union all 数据 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者董哥的黑板报的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、组合查询概述

多数SQL查询都只包含从一个或多个表中返回数据的单条select语句。MySQL也允许执行多个查询（多个select语句）。并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或符合查询。
有两种基本情况，其中需要使用组合查询：
- 1.在单个查询中从不同的表中返回类似结构的数据。
- 2.对单个表执行多个查询，按单个查询返回数据。

组合查询（union）与多个where条件

多数情况下，组合相同表的两个查询完成的工作与具有多个where子句条件的单条查询完成的工作相同。换句话说，任何具有多个where子句的select语句可以作为一个组合查询给出。

这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

可能union比where子句更加复杂（代码量也更多），但对于更复杂的过滤条件，或者从多个表中检索数据的情形，使用union可能会使处理更简单。

用例表

创建下面3个表，用来做演示案例：

CREATE TABLE t1 
(
  i INT NOT NULL,
  c VARCHAR(20) NOT NULL
);
CREATE TABLE t2 
(
  j INT NOT NULL,
  c VARCHAR(20) NOT NULL
);
CREATE TABLE t3 
(
  d DATETIME NOT NULL,
  k INT NOT NULL
);
INSERT INTO t1 VALUES(1,'red'),(2,'blue'),(3,'green');
INSERT INTO t2 VALUES(-1,'tan'),(1,'red');
INSERT INTO t3 VALUES('1904-01-01',100),('2004-01-01',200),('2004-01-01',200);

MySQL表组合查询：UNION、UNION ALL_合并查询结果

包含数据如下：

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;

MySQL表组合查询：UNION、UNION ALL_数据_02

二、创建UNION组合查询

可用union操作来组合数条SQL查询。利用union，可给出多条select语句，将它们的结果组合成单个结果集。
当然也可以使用多条where子句进行组合查询（见下演示案例）。

演示案例

要求：在products表中查询价格小于等于5的所有物品，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。

如果不使用组合查询：需要根据条件分别执行两次select语句才能查询出所有的结果。
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5;
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN(1001, 1002);
如果使用union组合查询：只需要使用union关键字将两个select组合在一起即可。
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN(1001, 1002);
如果不使用union，使用where子句进行组合查询：结果与union一致（但是结果的排列顺序不一致）。
SELECT vend_id, prod_id, prod_price FROM products 
WHERE prod_price <= 5 OR vend_id IN(1001, 1002);

三、UNION规则

union在进行合并时有几条规则需要注意：
- union必须由两条或两条以上的select语句组成，语句之间用关键字union分割（例如，如果组合4条select语句，将要使用3个union关键字）。
- union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
- union在组合查询时，使用的表不仅可以是相同的表，也可以是不同的表。
还有一些规则见下：

列名和数据类型

UNION结果集里的列名来自第一个SELECT里的列名。UNION中的第二个及后面的SLEECT必须选取相同个数的列，但各相应列不必具有相同的名字和数据类型(如果数据类型不一样，那么MySQL会进行必要的类型转换)。

例如，下面将3个表进行组合，后面t2、t3表的列内容都是根据t1表的i字段进行匹配的：
SELECT i FROM t1 UNION SELECT j FROM t2 UNION SELECT k FROM t3;
列是根据位置，而不是根据名字来匹配。例如，这也正是下面两条语句会返回不同结果的原因，尽管它们从两个表里査询到的是相同的值：
在各条语句的结果里，每一列的数据类型都是根据所选值的类型来确定的。

在第一条语句里，我们为第二个列选取的类型是字符串和日期。而结果是一个字符串列。

在第二条语句里，第一个列选取的类型是整数和日期，而第二个列选取的类型是字符串和整数。

对于这两种情况，最后的结果都是一个字符串列。
SELECT i, c FROM t1 UNION SELECT k, d FROM t3;
SELECT i, c FROM t1 UNION SELECT d, k FROM t3;

四、包含重复的行（UNION ALL）

取消重复的行（UNION的默认行为）

在上面的UNION演示案例中，我们可以看到两条select语句一共返回了9行，但是使用了union之后只返回了8行，因为其中有一行是在两个select都存在的，union默认将相同（重复）的行合并为一行了。

取消重复的行这是union的默认行为。

UNION DISTINCT与UNION是等价的，它们都只会保留不同的行。

UNION ALL

如果在查询中不想取消重复的行，可以使用“union all”来代替“union”。

如果把UNION或UNION DISTINCT与UNIONALL混合使用，那么所有的“不同联合”操作都会比其左边的所有UNION ALL操作获得更高的优先级。

UNION ALL与WHERE

前面说过，union几乎总是完成与多个where条件相同的工作。union all为union的一种形式，它完成where子句完成不了的工作。

如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用union all而不是where。

演示案例

取消重复的行（union）：

select vend_id,prod_id,prod_price from products
where prod_price <= 5
union
select vend_id,prod_id,prod_price from products
where vend_id in (1001,1002);

MySQL表组合查询：UNION、UNION ALL_组合查询_08

不取消重复的行（union all）：

select vend_id,prod_id,prod_price from products
where prod_price <= 5
union all
select vend_id,prod_id,prod_price from products
where vend_id in (1001,1002);

MySQL表组合查询：UNION、UNION ALL_合并查询结果_09

五、ORDER BY与LIMIT处理

ORDER BY

规则如下：
如果某个排序列是别名，那么在UNION末尾的ORDER BY子句必须引用这个别名。

此外，ORDER BY不能引用表名。如果需要按第一个SELECT语句里某个以table_name.col_name的形式指定的列来进行排序，那么必须为此列取一个别名，并在ORDER BY子句里引用此别名。

如果想将UNION结果作为一个整体进行排序，那么需要括号把每一个SELECT语句括起来，并在最后那个SELECT语句的后面再加上一个ORDER BY子句。因为UNION会使用第一个SELECT语句里的列名，所以ORDER BY子句必须引用那些名字，而不能引用最后那个SELECT语句里的列名。

例如，下面根据c进行排序，其中t3表的d列的值排在了t1表的c列值的前面：
(SELECT i, c FROM t1) UNION (SELECT k, d FROM t3) ORDER BY c;
当然，如果列名都是相同的，那么可以不使用括号，直接进行排序。例如，根据vend_id和prod_price字段对进行进行排序（order by放置到SQL语句的最后）。
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
union
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;

LIMIT

似地，如果想要限制UNION所返回行的数量，那么可以在语句末尾加上LIMIT子句：
(SELECT * FROM t1) UNION (SELECT * FROM t2) UNION (SELECT * FROM t3) LIMIT 2;

ORDER BY与LIMIT

子句ORDER BY和LIMIT还可以用在被括号括起来的单个SELECT里，从而只会对这条SELECT语句起作用：
(SELECT * FROM t1 ORDER BY i LIMIT 2) 
UNION (SELECT * FROM t2 ORDER BY j LIMIT 1) 
UNION (SELECT * FROM t3 ORDER BY d LIMIT 2);
在用括号括起来的单个SELECT语句里，ORDER BY只能与LIMIT—起使用，用以确定LIMIT将作用于哪些行。此时，它不会影响UNION的最终结果行的先后顺序。