定义

出现在另一个Select语句之内的Select语句形式被称为子查询

使用规则

  • 子查询必须“自身就是一个完整的查询”。即，它必须至少包括一个SELECT子句和FROM子句。

  • 子查询不能包括在ORDER BY子句中。因为ORDER BY字句只能对最终查询结果排序，如果显示的输出需要按照特定顺序显示，那么ORDER BY子句应该作为外部查询的最后一个子句列出。

  • 子查询“必须包括在一组括号中”，以便将它与外部查询分开。

  • 如果将子查询放在外部查询的WHERE或HAVING子句中，那么该子查询只能位于比较运算符的“右边”。

返回值类型

  • 标量子查询(scalar subquery)：返回1行1列一个值

  • 行子查询(row subquery)：返回的结果集是 1 行 N 列

  • 列子查询(column subquery)：返回的结果集是 N 行 1列

  • 表子查询(table subquery)：返回的结果集是 N 行 N 列

常见使用场景

WHERE型

通常为以下格式中一种：

  • WHERE expression [NOT] IN (subquery)

  • WHERE expression comparison_operator [ANY | ALL] (subquery)

  • WHERE [NOT] EXISTS (subquery)

注意以下几点

  • 谓词IN, ANY, ALL后的子查询往往是列子查询

  • EXISTS是存在性测试，其后的子查询往往是SELECT * FROM......

FROM型

通常格式为：SELECT * FROM (subquery)

子查询多为表子查询，子查询的结果是一张临时表

非相关子查询

定义

内部查询的执行独立于外部查询，内部查询仅执行一次，执行完毕后将结果作为外部查询的条件使用。（先执行内部查询，再执行外部查询）

示例

示例均按照MYSQL语法

标量子查询

select column1 + (select max(column2) from table2) 
from table1;


select column1, column2 
from table1 
where column1 > (select max(select column1 from table2);


行子查询

select column1, column2 
from table1 
where (column1, column2) = (select avg(column1),avg(column2) from table2);

列子查询

select column1, column2 
from table1 
where column1 in (select column1 from table2);


表子查询

select t.a + t.b 
from (select column1 as a, column2 as b from table1) t;

相关子查询

定义

在子查询中使用到了外部查询的表中的任何列。先执行外部查询，然后执行子查询。

相关子查询的执行步骤：

　　①先执行外部查询，得到的行叫做候选行

　　②使用某个候选行来执行子查询

　　③使用子查询的返回值来决定该候选行是出现在最终的结果集中还是被丢弃

　　④重复以上步骤2和3，将所有的候选行处理完毕，得到最终的结果

示例

得到项目是‘研发产品’的雇员的编号
mysql> select num
    -> from employee
    -> where '研发产品'=(
    -> 　　select function
    -> 　　from department
    -> 　　where d_id=employee.d_id);

  1. 主查询得到候选行，一行一行的拿去执行子查询；

  2. 主查询表employee的候选行的d_id和子查询的d_id匹配，返回值进行where过滤；

  3. 符合，加入最终结果集；

  4. 不符合，将候选行丢弃，接着进行处理下一个候选行。

常见数据库调研

InfluxDB

官方文档链接

InfluxDB支持的子查询种类：FROM型不相关子查询，且往往与聚合查询以及group by共同使用

基本用法

语法
SELECT_clause FROM ( SELECT_statement ) [...]
语法描述

InfluxDB首先执行子查询，再次执行主查询。

主查询围绕子查询，至少需要SELECT和FROM子句。

子查询显示在主查询的FROM子句中，它需要附加的括号。

InfluxQL支持在每个主查询中嵌套多个子查询：

SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]
                     ------------------   ----------------
                         Subquery 1          Subquery 2

InfluxQL不支持每个子查询中多个SELECT语句：

SELECT_clause FROM (SELECT_statement; SELECT_statement) [...]

如果一个子查询中多个SELECT语句，系统会返回一个解析错误。

示例

例一：计算多个MAX()值的SUM()
> SELECT SUM("max") FROM (SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location")
​
name: h2o_feet
time                   sum
----                   ---
1970-01-01T00:00:00Z   17.169

该查询返回location的每个tag值之间的最大water_level的总和。

InfluxDB首先执行子查询; 它计算每个tag值的water_level的最大值：

> SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location"
name: h2o_feet
​
tags: location=coyote_creek
time                   max
----                   ---
2015-08-29T07:24:00Z   9.964
​
name: h2o_feet
tags: location=santa_monica
time                   max
----                   ---
2015-08-29T03:54:00Z   7.205

接下来，InfluxDB执行主查询并计算这些最大值的总和：9.964 + 7.205 = 17.169。 请注意，主查询将max(而不是water_level)指定为SUM()函数中的字段键。

例二：计算两个field的差值的MEAN()
> SELECT MEAN("difference") FROM (SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare")
​
name: pet_daycare
time                   mean
----                   ----
1970-01-01T00:00:00Z   1.75

查询返回measurementpet_daycare``cats和dogs数量之间的差异的平均值。

InfluxDB首先执行子查询。 子查询计算cats字段中的值和dogs字段中的值之间的差值，并命名输出列difference：

> SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare"
​
name: pet_daycare
time                   difference
----                   ----------
2017-01-20T00:55:56Z   -1
2017-01-21T00:55:56Z   -49
2017-01-22T00:55:56Z   66
2017-01-23T00:55:56Z   -9

接下来，InfluxDB执行主要查询并计算这些差的平均值。请注意，主查询指定difference作为MEAN()函数中的字段键。

Druid

Druid官方文档链接

Druid SQL支持如下结构的SELECT查询，可以看出Druid支持FROM型表子查询

[ EXPLAIN PLAN FOR ]
[ WITH tableName [ ( column1, column2, ... ) ] AS ( query ) ]
SELECT [ ALL | DISTINCT ] { * | exprs }
FROM { <table> | (<subquery>) | <o1> [ INNER | LEFT ] JOIN <o2> ON condition }
[ WHERE expr ]
[ GROUP BY [ exprs | GROUPING SETS ( (exprs), ... ) | ROLLUP (exprs) | CUBE (exprs) ] ]
[ HAVING expr ]
[ ORDER BY expr [ ASC | DESC ], expr [ ASC | DESC ], ... ]
[ LIMIT limit ]
[ UNION ALL <another query> ]

同时，支持谓词IN的不相关子查询

x IN (subquery) 如果子查询返回x，则为True。这将转换为联接；有关详细信息，请参阅 查询转换
x NOT IN (subquery) 如果子查询没有返回x，则为True。这将转换为联接；有关详细信息，请参阅 查询转换

TDEngine

TDEngine官方文档链接

从 2.2.0.0 版本开始，TDengine 的查询引擎开始支持在 FROM 子句中使用非关联子查询（“非关联”的意思是，子查询不会用到父查询中的参数）。也即在普通 SELECT 语句的 tb_name_list 位置，用一个独立的 SELECT 语句来代替（这一 SELECT 语句被包含在英文圆括号内），于是完整的嵌套查询 SQL 语句形如：

SELECT ... FROM (SELECT ... FROM ...) ...;

说明：

  1. 目前仅支持一层嵌套，也即不能在子查询中再嵌入子查询。

  2. 内层查询的返回结果将作为“虚拟表”供外层查询使用，此虚拟表可以使用 AS 语法做重命名，以便于外层查询中方便引用。

  3. 目前不能在“连续查询”功能中使用子查询。

  4. 在内层和外层查询中，都支持普通的表间/超级表间 JOIN。内层查询的计算结果也可以再参与数据子表的 JOIN 操作。

  5. 目前内层查询、外层查询均不支持 UNION 操作。

  6. 内层查询支持的功能特性与非嵌套的查询语句能力是一致的。

    • 内层查询的 ORDER BY 子句一般没有意义，建议避免这样的写法以免无谓的资源消耗。

  7. 与非嵌套的查询语句相比，外层查询所能支持的功能特性存在如下限制：

    • 计算函数部分：

      1. 如果内层查询的结果数据未提供时间戳，那么计算过程依赖时间戳的函数在外层会无法正常工作。例如：TOP, BOTTOM, FIRST, LAST, DIFF。

      2. 计算过程需要两遍扫描的函数，在外层查询中无法正常工作。例如：此类函数包括：STDDEV, PERCENTILE。

    • 外层查询中不支持 IN 算子，但在内层中可以使用。

    • 外层查询不支持 GROUP BY。

TimeScaleDB

TimeScaleDB官方文档链接

With TimescaleDB, there's no need to learn a custom query language. TimescaleDB supports full SQL. This means you can put your SQL knowledge to good use and use the rich ecosystem of PostgreSQL tools you know and live.

TimeScaleDB支持PostgreSQL所有子查询语法

Cassandra

Cassandra官方文档链接

谷歌上搜索显示不支持子查询，官网文档(4.0版本)对查询的定义也显示不支持子查询。

select_statement::= SELECT [ JSON | DISTINCT ] ( select_clause | '*' )
    FROM `table_name`
    [ WHERE `where_clause` ]
    [ GROUP BY `group_by_clause` ]
    [ ORDER BY `ordering_clause` ]
    [ PER PARTITION LIMIT (`integer` | `bind_marker`) ]
    [ LIMIT (`integer` | `bind_marker`) ]
    [ ALLOW FILTERING ]
select_clause::= `selector` [ AS `identifier` ] ( ',' `selector` [ AS `identifier` ] )
selector::== `column_name`
    | `term`
    | CAST '(' `selector` AS `cql_type` ')'
    | `function_name` '(' [ `selector` ( ',' `selector` )_ ] ')'
    | COUNT '(' '_' ')'
where_clause::= `relation` ( AND `relation` )*
relation::= column_name operator term
    '(' column_name ( ',' column_name )* ')' operator tuple_literal
    TOKEN '(' column_name# ( ',' column_name )* ')' operator term
operator::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
group_by_clause::= column_name ( ',' column_name )*
ordering_clause::= column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

Doris

Doris官方文档链接

不相关子查询

支持谓词 IN和 EXISTS

SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);
SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);

支持标量子查询

1、不相关标量子查询，谓词为=号。例如输出最大工资的人的信息
SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);
2、不相关标量子查询，谓词为>,<等。例如输出比平均工资高的人的信息
SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);
3、标量子查询作为普通函数的参数
SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));

相关子查询

支持谓词 IN和 EXISTS

SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);
SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);

支持标量子查询

1、相关标量子查询。例如输出各个部门工资最高的信息
SELECT name FROM table a WHERE salary = （SELECT MAX(salary) FROM table b WHERE b.部门= a.部门）;
  • No labels