MySQL8.0.15中关于UNION关键字的用法及特点

YinLixue
839 阅读3分钟

概述

UNION关键字将多个查询的结果集合并到一个结果集。

查询结果集列名以及数据类型

  • 列名： 最终结果集的列名是根据第一个查询语句的列名产生的

    The column names for a UNION result set are taken from the column names of the first SELECT statement.

  • 数据类型： 各查询语句对应的列应该具有相同的数据类型或者类型相互兼容。

比较 UNION ALL 与 UNION DISTINCT

UNION 与 UNION ALL 的关系，从字面上就能看出，后者只是在前者的基础上增加了一个条件，在官网上关于UNION关键字的用法是这样给出的：

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

可以看出，ALL是一个可选条件，和 DISTINCT 是并列的关系。如果两个条件都不选，那么 UNION 默认将去除并集中重复的行（所以这里是根据行去重的，官网上的描述是:

By default, duplicate rows are removed from UNION results.

），和DISTINCT关键字效果是相同的，只不过加上DISTINCT关键字语义更明确。

两者可以混合使用，但是注意，UNION DISTINCT会覆盖掉他左边的所有UNION ALL。也就是说在UNION DISTINCT左边的UNION ALL中出现的重复结果，都会被此UNION DISTINCT去掉。

UNION 语句中的 ORDER BY 和 LIMIT

  1. 单个查询语句中的 ORDER BY 和 LIMIT
    • 首先，ORDER BY关键字对最终结果集的顺序是没有影响的，UNION返回的是一个无序的结果集，如果ORDER BY在查询语句中单独出现，那么他将被优化掉，因为起不到任何作用
    • 所以，一般ORDER BY语句会与LIMIT语句结合使用，用于返回特定顺序下的某个范围内的元组 
      (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
    • 注意：如果在查询语句中用到了ORDER BY和LIMIT，那么SELECT语句就要加括号，否则语法检查报错。在MySQL5.7及之后就强制要求有括号。
  2. 位于整个UNION后的 ORDER BY 和 LIMIT

    • 如果要sort或limit整个union的结果，把每个单独的查询语句用括号括起来，然后将 ORDER BY 和 LIMIT放在最后一个查询语句的后面即可，例如：

       (SELECT a FROM t1 WHERE a=10 AND B=1)
  UNION
  (SELECT a FROM t2 WHERE a=11 AND B=2)
  ORDER BY a LIMIT 10;

    • 如果不加括号， ORDER BY 和 LIMIT默认就是过滤整个UNION的结果集，和上面加了括号的效果一致

    • 在最后起整体过滤作用的 ORDER BY 语句不能引用表名，而且只能引用第一个查询语句中的列名。可以为 ORDER BY 依据列定义别名，但是定义了别名之后就只能使用别名。

    • 如果想要最终结果集，按照每条单独的查询语句结果集一个一个的排列，可以使用如下方式：在每个单独的查询中添加一个附加列来作为排序依据

        (SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
  UNION
  (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

    • 在上面的基础上，如果还要为每个单独的查询结果排序，那么可以直接在 ORDER BY 子句后添加一个辅助排序依据，如下

      (SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
  3. 小结 如果想在单独的查询语句中用 ORDER BY 和 LIMIT，那么必须在单独的查询语句上加括号。否则，如果 ORDER BY 和 LIMIT语句只出现最后，那么他将被认为是过滤最终结果集的语句；如果， ORDER BY 和 LIMIT不仅出现在最后一个查询语句后，那么将会报语法错误。

参考

dev.mysql.com/doc/refman/…

avatar
文章
阅读
粉丝