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 t1SELECT ... FROM v1SELECT ... 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 = 1id >= 1id > 1id <= 1id < 1id <> 1id != 1
  • AND多个条件:id = 1 AND name = "peter"id = 1 && name = "peter"
  • OR多个条件:id = 1 OR id = 2id = 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 NULLid 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));
  8. # sql 2
  9. SELECT origin_state, NULL, NULL, sum(package_weight)
  10. FROM shipping GROUP BY origin_state
  11. UNION ALL
  12. SELECT origin_state, origin_zip, NULL, sum(package_weight)
  13. FROM shipping GROUP BY origin_state, origin_zip
  14. UNION ALL
  15. SELECT NULL, NULL, destination_state, sum(package_weight)
  16. 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);
  5. # sql 2
  6. SELECT origin_state, destination_state, sum(package_weight)
  7. FROM shipping
  8. GROUP BY GROUPING SETS (
  9. (origin_state, destination_state),
  10. (origin_state),
  11. (destination_state),
  12. ());

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);
  5. # sql 2
  6. SELECT origin_state, origin_zip, sum(package_weight)
  7. FROM shipping
  8. 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);
  9. # sql 2
  10. SELECT
  11. val,
  12. ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  13. RANK() OVER (ORDER BY val) AS 'rank',
  14. DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
  15. FROM numbers;

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

  1. WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...
  2. # window_spec
  3. [window_name] [partition_clause] [order_clause] [frame_clause]
  4. # partition_clause + order_clause + frame_clause
  5. 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;
  6. # sql 2
  7. WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
  8. 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);
  2. # NOT IN 与 <> ALL 等价
  3. SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
  4. 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);
  3. # 多层时从里到外寻找关联列,如下面语句 最内层的x.column2 找到是t2的列
  4. SELECT column1 FROM t1 AS x
  5. WHERE x.column1 = (SELECT column1 FROM t2 AS x
  6. WHERE x.column1 = (SELECT column1 FROM t3
  7. 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