wechat-banner.png

SQL概述

SQL的全称是Structured Query Language（结构化查询语言），是最早于1970年代提出并广泛应用于访问和管理关系型数据库（如Oracle/MySQL）的标准计算机语言。 基于SQL的全面强大的功能及简单易用的特性，其历经50年风雨一直长盛不衰；很多近年来才快速发展起来的大数据处理系统如Presto/Flink/Spark/Hive等也实现了SQL或类SQL的语言。

基于具体的功能可以对SQL做进一步的细分，常用的有：

  • 数据定义语言（DDL，Data Definition Language）：创建新表或修改、删除表，关键字包括CREATE、ALTER和DROP等
  • 数据操作语言（DML，Data Manipulation Language）：用于添加、修改和删除数据，关键字包括INSERT、UPDATE和DELETE等
  • 数据查询语言（DQL，Data Query Language）：用于从表中获得数据，关键字是SELECT

实际应用中，我们通常是编写SQL语句，提交给数据库或大数据系统来执行，然后获取执行结果；有几个容易搞迷糊的概念如下：

  • Statement：语句，通常指整个SQL文本
  • Clause：子句，通常指SQL文本中的一部分，如From子句、Where子句、Group By子句
  • Query：查询，通常指SQL文本在系统中的一次执行实例
  • Database：库，一个库可以有多个表；含义类似的有：模式（Schema）
  • Table：表，一个表可以有很多行；含义类似的有：关系（Relation）
  • Row：行，一行可以有很多列；含义类似的有：记录（Record）、条目（Item）
  • Column，列，不再细分的具体值；含义类似的有：字段（Field）

需要强调的是，不同的数据库或大数据系统中的SQL会有一定的实现差异或特有的扩展功能，本文着重于介绍通识性的概念和用法，实际应用时请结合阅读相关系统的官方文档。

SQL查询语句

SQL查询语句用于从零个或多个表中检索数据行，其可以包含子查询语句，还可以用UNION等将多个SELECT语句组合起来获取结果，也可以用WITH子句定义在SELECT中可访问的公共表表达式（CTE，Common Table Expressions）。

OLTP领域追求SQL查询语句简单高效，很多功能特性都会避免使用，甚至不惜拆分多次执行以最大化数据库系统的并发能力；然而，在大数据、OLAP领域虽然也很期待查询能很快执行完成，但查询语句中往往直接夹杂着数据分析型业务逻辑或者ETL需求，从而导致查询语句变得非常长且复杂；因此我们来详细整理一下组成复杂查询语句的各个子句，熟练掌握这些子句才能比较容易看懂并写出满足需求的复杂查询SQL。

SQL查询语句的基本语法结构如下：

  1. [ WITH with_query [, ...] ]
  2. SELECT [ ALL | DISTINCT ] select_expr [, ...]
  3. [ FROM from_item [, ...] ]
  4. [ WHERE condition ]
  5. [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
  6. [ HAVING condition]
  7. [ WINDOW window_name AS ( window_definition ) [, ...] ]
  8. [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
  9. [ ORDER BY {column_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
  10. [ LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT子句

SELECT子句是查询语句中必不可少的部分，由SELECT关键字及至少一个标识要检索列的表达式构成，多个表达式之间以英文逗号分隔；支持的表达式包括但不限于如下情况：

  • 字面量，如 SELECT 1, "hello"
  • 所有列的简写，如 SELECT * FROM ...
  • 限定表名或表别名的列，如 SELECT t1.name, t2.* FROM t1, table2 as t2
  • 给选定的列指定别名，如 SELECT name as alias_name FROM ...
  • 用反引号包裹列名或限定表名，如 SELECTname,t1.ageFROM t1
  • 运算表达式，如 SELECT 1+1, age+10 FROM ...
  • 对列值做运算的函数，如 SELECT CONCAT(last_name,', ',first_name) FROM ...
  • 关键字ALL/DISTINCT限定是否返回重复行 ，默认是ALL返回所有行，如 SELECT DISTINCT name FROM ...

运算符与函数

前面提到复杂查询往往夹杂着业务逻辑或ETL需求，因此在SELECT子句中对原始列数据值的处理是必不可少的。 要灵活高效的处理数据值就需要有大量运算符与函数，常用的运算符与函数列举如下：

  • 算术运算符：+, -, *, /, DIV, %/MOD
  • 数值函数：ABS、FLOOR、CRC32、RAND、LOG、SIN、COS、TAN 等等
  • 日期与实际函数：STR_TO_DATE、DATE_FORMAT、ADDDATE、CURDATE、TIMESTAMP 等等
  • 字符串函数：CONCAT、LEFT、LENGTH、LIKE、LTRIM、REPLACE、SPLIT、TRIM、UPPER 等等
  • 位操作符与函数：&, ~, |, ^, >>, <<
  • CAST类型转换函数：CAST(expr AS type)
  • 控制流函数：CASE WHEN、IF、IFNULL、NULLIF 等等
  • 聚合函数：GROUP BY子句中介绍
  • 窗口函数：WINDOW子句中介绍
  • 其他函数：JSON、GEO、IP、正则、加解密、压缩 等等

FROM子句

FROM子句用于指定从哪些表中检索数据；如指定多个表则说明使用了连接 Join（联合查询也是Join），支持的书写方式有：

  • 不指定表 SELECT 1, "hello"
  • 指定表名、视图名、临时表名 SELECT ... FROM t1、 SELECT ... FROM v1、 SELECT ... FROM with1
  • 指定表别名 SELECT ... FROM table2 as t2
  • 多表联合查询 SELECT ... FROM t1, t2, t3
  • 括号包含的子查询 SELECT ... FROM (SELECT ... FROM ...)
  • 多表连接查询，JOIN指定连接的左右表，ON指定连接字段 SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2
  • 指定内联表 SELECT * FROM (VALUES 13, 42)

JOIN 连接

对于JOIN 连接查询，更详细的语法格式如下：

  1. table_name join_type table_name [ ON join_condition | USING ( join_column [, ...] ) ]

join_type有以下几种具体的连接方式：

  • [ INNER ] JOIN：内连接，也叫等值连接，只返回两个表中联结字段相等的行
  • LEFT [ OUTER ] JOIN：左（外）连接，返回包括左表中的所有记录和右表中联结字段相等的记录
  • RIGHT [ OUTER ] JOIN：右（外）连接，返回包括右表中的所有记录和左表中联结字段相等的记录
  • FULL [ OUTER ] JOIN：全（外）连接，返回包括左表和右表中的所有记录
  • CROSS JOIN：交叉连接，返回两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积

USING用于简化两个表具有相同的连接字段时书写，如下面两种写法是等价的：

  • SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2
  • SELECT * FROM t1 JOIN t2 USING (c1, c2)

VALUES 内联表

VALUES用于创建一个没有列名的匿名表，但是可以使用带有列别名的AS子句来命名表和列，用法如下：

  • 单个列：SELECT * FROM (VALUES 1, 2, 3)
  • 多个列：SELECT * FROM (VALUES (1,’a’), (2,’b’), (3,’c’))
  • 给列命名：SELECT * FROM (VALUES (1,’a’), (2,’b’), (3,’c’)) as t(id, name)

WHERE子句

WHERE子句用于指定选择数据行时必须满足的一个或多个条件；WHERE条件是一个表达式，计算结果为true则选择该行，没有WHERE子句则选择所有行。Where条件中可以使用除分组聚合函数外的系统支持的函数，表达式常用写法如下：

  • 单个比较条件：id = 1、id >= 1、id > 1、id <= 1、id < 1、id <> 1、id != 1
  • AND多个条件：id = 1 AND name = "peter"、id = 1 && name = "peter"
  • OR多个条件：id = 1 OR id = 2、id = 1 || id = 2
  • 嵌套多个条件：(id = 1 OR id = 2) AND name = "peter"
  • IN/NOT IN：id IN (1,2,3)、id NOT IN (1,2,3)
  • IS NULL / IS NOT NULL：name IS NULL、id IS NOT NULL
  • BETWEEN AND：id BETWEEN 10 AND 100
  • LIKE：name LIKE "pet*"
  • EXISTS/NOT EXISTS：EXISTS (SELECT ...) 、NOT EXISTS (SELECT ...)

GROUP BY子句

GROUP BY 子句用于指定根据某列或某几列对查询结果进行分组，通常是为了分组后对数据做聚合(Aggregation)运算；用法举例：

  • 按单个字段分组计算总数：SELECT name, COUNT(*) FROM orders GROUP BY name
  • 按多个字段分组计算平均数：SELECT name, city, AVG(age) FROM users GROUP BY name, city;
  • GROUP BY子句中使用序号来引用所需列：SELECT name, city, AVG(age) FROM users GROUP BY 1, 2;

注意：部分系统的实现中SELECT子句中必须使用标准聚合函数（SUM/AVG/COUNT/ARBITRARY等）处理非分组的列，即所有SELECT的列必须是聚合函数或GROUP BY子句中出现的列，否则会报错；而有些系统的实现会对于不在GROUP BY子句中且没有用聚合函数处理的列随机出一行数据。

GROUPING SETS

GROUPING SETS 用于在同一个查询中指定多个GROUP BY的集合，作用相当于多个GROUP BY查询的UNION ALL组合，不属于单个GROUP BY的列被设置为NULL；但如果数据源数据会变化，多个UNION ALL因为多次读取数据所以可能并不等价。

如不考虑数据变化，下面两个SQL等价：

  1. # sql 1
  2. SELECT origin_state, origin_zip, destination_state, sum(package_weight)
  3. FROM shipping
  4. GROUP BY GROUPING SETS (
  5.     (origin_state),
  6.     (origin_state, origin_zip),
  7.     (destination_state));
  9. # sql 2
  10. SELECT origin_state, NULL, NULL, sum(package_weight)
  11. FROM shipping GROUP BY origin_state
  12. UNION ALL
  13. SELECT origin_state, origin_zip, NULL, sum(package_weight)
  14. FROM shipping GROUP BY origin_state, origin_zip
  15. UNION ALL
  16. SELECT NULL, NULL, destination_state, sum(package_weight)
  17. FROM shipping GROUP BY destination_state;

CUBE

CUBE 用于列出所有可能的分组集，下面两个SQL等同：

  1. # sql 1
  2. SELECT origin_state, destination_state, sum(package_weight)
  3. FROM shipping
  4. GROUP BY CUBE (origin_state, destination_state);
  6. # sql 2
  7. SELECT origin_state, destination_state, sum(package_weight)
  8. FROM shipping
  9. GROUP BY GROUPING SETS (
  10.     (origin_state, destination_state),
  11.     (origin_state),
  12.     (destination_state),
  13.     ());

ROLLUP

ROLLUP 用于以层级的方式列出分组集，下面两个SQL等同：

  1. # sql 1
  2. SELECT origin_state, origin_zip, sum(package_weight)
  3. FROM shipping
  4. GROUP BY ROLLUP (origin_state, origin_zip);
  6. # sql 2
  7. SELECT origin_state, origin_zip, sum(package_weight)
  8. FROM shipping
  9. GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ());

GROUPING() 函数

GROUPING() 函数 用于返回一个转换为十进制的二进制位的集合，指示分组中出现哪些列，也就是可以用来标记该行结果是属于哪个GROUPING SET。它必须与GROUPING SETS、CUBE、ROLLUP或GROUP BY一起使用，其参数必须与相应GROUPING SETS、CUBE、ROLLUP或GROUP BY子句中引用的列精确匹配。

  1. GROUPING(col1, ..., colN) -> bigint

为了计算特定行的二进制位的集合，将位分配给参数列，最右边的列是最低有效位。对于给定的分组，如果对应的列包含在分组中，则该位设置为0，否则设置为1。例如下面的查询，第一个分组只包括origin_state列，而不包括origin_zip和destination_state列；为该分组构造的位集是011，其中最高有效位表示origin_state。

  1. SELECT origin_state, origin_zip, destination_state, SUM(package_weight),
  2.        GROUPING(origin_state, origin_zip, destination_state)
  3. FROM shipping
  4. GROUP BY GROUPING SETS (
  5.         (origin_state),                                    #  011 => 3
  6.         (origin_state, origin_zip),            #  001 => 1
  7.         (destination_state));                        #  110 => 6

聚合运算函数

常用的聚合运算函数有：

  • ARBITRARY：随机返回一组数据中的任意一个值
  • AVG：该函数用于计算平均值
  • COUNT：该函数用于计算记录数
  • MAX：该函数用于计算最大值
  • MIN：该函数用于计算最小值
  • SUM：该函数用于计算汇总值
  • GROUP_CONCAT：该函数用于将GROUP BY返回结果中属于同一个分组的值连接起来，返回一个字符串结果
  • STD或STDDEV：返回数值的样本标准差
  • STDDEV_POP：返回数值的总体标准差
  • STDDEV_SAMP：返回一组数值（整数、小数或浮点）的样本标准差。
  • VAR_POP（标准SQL函数）：返回一组数值（整数、小数或浮点）的总体方差
  • VAR_SAMP：返回一组数值（整数、小数或浮点）的样本方差

一些有用的特性：

  • COUNT(*)（针对全表）将返回表格中所有存在的行的总数包括值为NULL的行
  • COUNT(列名)（针对某一列）将返回表格中某一列除去NULL以外的所有行的总数
  • COUNT(DISTINCT 列名)，得到的结果将是除去值为NULL和重复数据后的结果
    • 如COUNT(DISTINCT orderstatus) 计算不重复且不为NULL的订单状态有多少个
  • SUM 空行为NULL，SUM(NULL) 为NULL，SUM(0) 为0
  • COUNT 空行为0，COUNT(NULL) 为0，COUNT(0) 为0
  • 有GROUP BY时，空结果被分为0个分组，求COUNT输出空
  • 无GROUP BY时，空结果在默认分组，求COUNT输出0

HAVING子句

HAVING子句 用于指定过滤分组行（GROUP BY后的行）条件；WHERE子句会在应用GROUP BY之前就过滤掉数据行，而HAVING子句则过滤 GROUP BY创建的分组行，因此其条件表达式中的列必须是GROUP BY子句中出现的分组列、处理分组列的函数、处理其他列的聚合函数。用法如下：

  • 分组列：... GROUP BY name HAVING ( name = "peter" )
  • 处理分组列的函数： ... GROUP BY name HAVING ( UPPER(name) = "PETER" )
  • 处理其他列的聚合函数：... GROUP BY name HAVING ( name = "peter" or AVG(age) > 20 )
  • 没有GOURP BY时全局聚合函数： ... HAVING ( AVG(age) > 20 )

WINDOW子句

WINDOW子句 用于定义一个可以在OVER关键字触发窗口函数时引用的窗口；也可以将窗口定义直接写在OVER关键字之后而不使用WINDOW子句，如下两个示例语句是等价的：

  1. # sql 1
  2. SELECT
  3.   val,
  4.   ROW_NUMBER() OVER w AS 'row_number',
  5.   RANK()       OVER w AS 'rank',
  6.   DENSE_RANK() OVER w AS 'dense_rank'
  7. FROM numbers
  8. WINDOW w AS (ORDER BY val);
  10. # sql 2
  11. SELECT
  12.   val,
  13.   ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  14.   RANK()       OVER (ORDER BY val) AS 'rank',
  15.   DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
  16. FROM numbers;

WINDOW子句书写在在HAVING子句之后、 ORDER BY子句之前，同样窗口函数也是在HAVING子句和ORDER BY子句之间运行，语法如下：

  1. WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...
  3. # window_spec
  4. [window_name] [partition_clause] [order_clause] [frame_clause]
  6. # partition_clause + order_clause + frame_clause
  7. PARTITION BY a ORDER BY b RANGE|ROWS BETWEEN start AND end

窗口定义包含以下三个部分

  • 分区规范：用于将输入行分散到不同的分区中，过程和GROUP BY子句的分散过程相似
  • 排序规范：决定输入数据行在窗口函数中执行的顺序
  • 窗口区间：指定计算数据的窗口边界。窗口区间支持RANGE、ROWS两种模式：
    • RANGE按照计算列值的范围进行定义，ROWS按照计算列的行数进行范围定义
    • RANGE、ROWS 中可以使用 BETWEEN start AND end 指定边界可取值：
      • CURRENT ROW，当前行
      • N PRECEDING，前n行
      • UNBOUNDED PRECEDING，直到第1行
      • N FOLLOWING，后n行
      • UNBOUNDED FOLLOWING，直到最后1行

查询示例：

  1. SELECT year, country, profit,
  2.     SUM(profit) OVER (
  3.     PARTITION BY country ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  4.   ) AS slidewindow 
  5. FROM testwindow;

常用窗口运算函数：

  • 聚合函数
  • 排序函数
    • CUME_DIST：返回一组数值中每个值的累计分布
    • RANK：返回数据集中每个值的排名
    • DENSE_RANK：返回一组数值中每个数值的排名
    • NTILE：将每个窗口分区的数据分散到桶号从1到n的n个桶中
    • ROW_NUMBER：根据行在窗口分区内的顺序，为每行数据返回一个唯一的有序行号，行号从1开始
    • PERCENT_RANK：返回数据集中每个数据的排名百分比，其结果由(r - 1) / (n - 1)计算得出。其中r为RANK()计算的当前行排名， n为当前窗口分区内总的行数
  • 值函数
    • FIRST_VALUE：返回窗口分区第1行的值
    • LAST_VALUE返回窗口分区最后1行的值
    • LAG：返回窗口内距离当前行之前偏移offset后的值
    • LEAD：返回窗口内距离当前行偏移offset后的值
    • NTH_VALUE：返回窗口内偏移指定offset后的值，偏移量从1开始

ORDER BY子句

ORDER BY子句用于指定一个或多个表达式来对结果集进行排序，在GROUP BY或HAVING子句之后执行； 更详细的语法如下：

  1. ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

用法举例：

  • 单个列：ORDER BY id ASC
  • 多个列：ORDER BY id ASC, status DESC
  • 指定NULL值排在最前面：ORDER BY id ASC NULLS FIRST
  • 使用序号来引用所需列：SELECT name, city ... ORDER BY 1, 2;

LIMIT子句

LIMIT子句用于限制结果集中的行数，LIMIT子句可以和ORDER BY子句一起使用，对结果集排序后取前多少个，也就是TOP N；还可以指定offset来跳过结果集前面的行用于查询结果分页；对于分布式系统来说，如果没有和ORDER BY子句配合使用，多次执行返回的数据将会是不同的；LIMIT ALL与省略LIMIT子句相同； 用法举例：

  • 不限制行数：SELECT * FROM orders LIMIT ALL
  • 单独使用：SELECT * FROM orders LIMIT 5
  • 和ORDER BY配合使用：SELECT * FROM orders ORDER BY id DESC LIMIT 5
  • 指定offeset跳过行：SELECT * FROM orders LIMIT 100, 5
  • 使用OFFSET子句跳过行：SELECT * FROM orders OFFSET 100 LIMIT 5

WITH子句

WITH子句用于定义一个或者多个在主查询中使用的命名临时表，以扁平化嵌套查询或简化子查询。在部分系统的实现中，WITH定义的临时表可以作为CTE（通用表表达式 Common Table Express）从而只需执行一次以提高性能，但也有的系统只是将其对应的SQL内联到用到的地方从而会被执行多次，具体需要查看对应系统的文档。

在类MySQL系统的语法中，WITH子句通常放在SELECT、UPDATE、DELETE语句之前，对于包含SELECT语句的语句 则是紧挨着SELECT：

  • WITH…SELECT…
  • WITH…UPDATE…
  • WITH…DELETE…
  • SELECT…WHERE id IN(WITH…SELECT…)…
  • SELECT * FROM (WITH … SELECT …) AS dt …
  • INSERT…WITH…SELECT…
  • REPLACE…WITH…SELECT…
  • CREATE TABLE…WITH…SELECT…
  • CREATE VIEW…WITH…SELECT…
  • EXPLAIN…WITH…SELECT…

下面的等价语句展示了WITH的用法

  1. # sql 1
  2. SELECT a, b
  3. FROM (
  4.   SELECT a, MAX(b) AS b FROM t GROUP BY a
  5. ) AS x;
  7. # sql 2
  8. WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
  9. SELECT a, b FROM x;

WITH子句可用于多子查询：

  1. WITH
  2.   t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
  3.   t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
  4. SELECT t1.*, t2.*
  5. FROM t1
  6. JOIN t2 ON t1.a = t2.a;

WITH子句中定义的关系可以互相连接

  1. WITH
  2.   x AS (SELECT a FROM t),
  3.   y AS (SELECT a AS b FROM x),
  4.   z AS (SELECT b AS c FROM y)
  5. SELECT c FROM z;

子查询

子查询是一条语句中的SELECT语句；

标量子查询

标量子查询是和外围查询不相关且只返回零行或一行一列的子查询，标量子查询产生多个行或多个列是错误的，如果没有产生行则返回值为NULL。标量子查询通常用于和外围查询的某个列做比较，支持= > < >= <= <> != <=> 等比较运算符：

  1. SELECT name FROM nation
  2. WHERE regionkey = (SELECT max(regionkey) FROM region)

IN、ANY/SOME、ALL 子查询

IN 用于判断子查询产生的任何值是否有等于所提供的表达式，IN后面的子查询必须恰好生成一个列:

  1. SELECT name FROM nation WHERE regionkey IN (SELECT regionkey FROM region)

ANY关键字必须跟在比较操作符后面，意思是“如果子查询返回的列中的任何值的比较结果为TRUE，则返回TRUE”，SOME 是ANY的别名 :

  1. SELECT name FROM nation WHERE regionkey = ANY (SELECT regionkey FROM region); # 子查询里 = ANY 和 IN 等价
  2. SELECT name FROM nation WHERE regionkey > SOME (SELECT regionkey FROM region);

ALL关键字也必须跟在比较操作符后面，意思是“如果子查询返回的列中的所有值的比较结果都是TRUE，则返回TRUE。”例如：

  1. SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
  3. # NOT IN 与 <> ALL 等价
  4. SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); 
  5. SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

EXISTS 和 NOT EXISTS 子查询

如果子查询返回任何行，EXISTS子查询为TRUE，NOT EXISTS子查询为FALSE：

  1. SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

上面的例子中如果t2包含任何行，即使是只有NULL值的行，EXISTS条件为TRUE；这样的查询实际上没有什么意义，通常EXISTS/NOT EXISTS 子查询中的条件总是和外面的查询有关：

  1. SELECT DISTINCT store_type FROM stores WHERE EXISTS (
  2.   SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);

关联子查询

如上面例子中，当子查询引用子查询之外的列时称为关联子查询。逻辑上来说，关联子查询将对周围查询中的每一行计算子查询，在子查询的任何单个求值过程中，引用的列都是常量。 关联子查询有时候会比较复杂，部分系统的实现对关联查询可能支持并不完善，这里不做过多展开，请查阅相关系统的文档。

  1. SELECT * FROM t1 WHERE column1 = ANY (
  2.   SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
  4. # 多层时从里到外寻找关联列，如下面语句 最内层的x.column2 找到是t2的列
  5. SELECT column1 FROM t1 AS x
  6.   WHERE x.column1 = (SELECT column1 FROM t2 AS x
  7.     WHERE x.column1 = (SELECT column1 FROM t3
  8.       WHERE x.column2 = t3.column1));

集合查询

集合查询是指将多个SELECT语句的结果组合成一个结果集，有 UNION、INTERSECT和EXCEPT 三种集合运算。集合运算将null值作为普通值处理，即如有两行数据的列值都为null会被认为是重复行。

  • UNION：返回两个查询表达式的集合运算。
  • UNION ALL：ALL关键字用于保留UNION中产生的重复行。
  • INTERSECT：返回只有在两个集合中同时出现的行，返回结果将删除两个集合中的重复行。
  • EXCEPT：先删除两个集合中重复的数据行，返回只在第一个集合中出现且不在第二个集合中出现的行。
    1. SELECT * FROM t1 UNION SELECT * FROM t2;
    2. SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    3. SELECT * FROM t1 INTERSECT SELECT * FROM t2;
    4. SELECT * FROM t1 EXCEPT SELECT * FROM t2;
    要将ORDER BY或LIMIT子句应用于单个SELECT，请将SELECT用括号括起来，并将子句放在括号中：
    1. (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
    2. UNION
    3. (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
    单个语句中的ORDER BY并不意味着这些行在最终结果中出现的顺序，UNION在默认情况下会生成无序的行集；如果上面的语句有ORDER BY没有LIMIT，则ORDER BY其实没有意义可以优化掉。要使用ORDER BY或LIMIT子句对整个UNION结果进行排序或限制，请将各个SELECT语句括起来，并将ORDER BY或LIMIT放在最后一个语句的后面：
    1. (SELECT a FROM t1 WHERE a=10 AND B=1)
    2. UNION
    3. (SELECT a FROM t2 WHERE a=11 AND B=2)
    4. ORDER BY a LIMIT 10;

参考资料

wechat-gzh.png