跳到主要内容

子查询

子查询（Subquery）是嵌套在另一个查询（通常是 SELECT 语句）中的 SQL 查询。它可以用在 SELECT、FROM、WHERE 或 HAVING 子句中，为外部查询提供数据或条件。子查询的使用使得 SQL 查询变得更加灵活和强大，因为它们允许我们在单个查询中解决更复杂的问题。

子查询的一些重要特征如下：

  1. 子查询的位置：子查询可以放在多个 SQL 子句中，如 SELECT、WHERE、HAVING 和 FROM 子句。它们可以与 SELECT、UPDATE、INSERT、DELETE 语句以及表达式运算符（如比较运算符 =、>、<、<=，以及 IN、EXISTS 等）一起使用。

  2. 主查询与子查询的关系：子查询是嵌套在另一个查询内部的查询。外部查询被称为主查询，而内部查询则被称为子查询。

  3. 执行顺序：当子查询是独立的（即不依赖于外部查询的结果）时，它通常首先执行。当存在相关性时，解析器会根据需要实时决定先执行哪个查询，并相应地使用子查询的输出。

  4. 括号的使用：子查询必须用括号括起来，以区分它们是嵌套在另一个查询中。

下面我们分别用 t1 和 t2 表以及相关 SQL，介绍子查询的基本特性和用法。建表语句如下：

create table t1
(
    c1 bigint, 
    c2 bigint
)
DISTRIBUTED BY HASH(c1) BUCKETS 3
PROPERTIES ("replication_num" = "1");

create table t2
(
    c1 bigint, 
    c2 bigint
)
DISTRIBUTED BY HASH(c1) BUCKETS 3
PROPERTIES ("replication_num" = "1");

子查询的分类​

按照子查询返回数据的特性分类​

按照子查询返回数据的特性，可分为标量和非标量子查询：

1. 标量子查询

子查询一定返回一个单一的值（本质上等价于一个一行一列的 Relation）。如果子查询没有任何数据返回，则返回 NULL 值。标量子查询理论上可以出现在任何允许单值表达式出现的地方。

2. 非标量子查询

子查询返回一个 Relation（不同于标量子查询的返回值，该 Relation 可包含多行多列）。如果子查询没有任何数据返回，则返回空集（0 行）。非标量子查询理论上可以出现在任何允许关系（集合）出现的地方。

以下分别对标量和非标量子查询举例说明（对于两个括号内的子查询，当 t2 是空表时，两个子查询返回结果不同）。

-- 标量子查询，当 t2 是空表时，子查询返回标量值 null  
select * from t1 where t1.c1 > (select sum(t2.c1) from t2);  
  
-- 非标量子查询，当 t2 是空表时，子查询返回 empty set (0 rows)  
select * from t1 where t1.c1 in (select t2.c1 from t2);

按照子查询是否引用了外部查询的列分类​

按照子查询是否引用了外部查询的列，可分为关联子查询和非关联子查询

1. 非关联子查询

子查询没有引用外部查询的任何列。非关联子查询常常可以独立运算，并一次性返回相应结果供外部查询使用。

2. 关联子查询

子查询引用了主查询（又称为外部查询）的一个或多个列（引用的外部列常常在子查询的 WHERE 条件中）。关联子查询常常可以看做是对外部关联的表的一个过滤操作，因为对于外部表的每一行数据，都会对子查询进行运算，并返回相应结果。

以下分别对关联和非关联子查询举例说明：

-- 关联子查询，子查询内部使用了外部表的列 t1.c2  
select * from t1 where t1.c1 in (select t2.c1 from t2 where t2.c2 = t1.c2);  
  
-- 非关联子查询，子查询内部没有使用任何外部表 t1 的列  
select * from t1 where t1.c1 in (select t2.c1 from t2);

Doris 支持的子查询​

Doris 支持所有的非关联子查询，对关联子查询（有部分限制）的支持如下：

  • 支持在 WHERE和 HAVING子句中的关联标量子查询。

  • 支持在 WHERE和 HAVING子句中的关联的 IN、NOT IN、EXISTS、NOT EXISTS 非标量子查询。

  • 支持在SELECT列表中的关联标量子查询。

  • 对于嵌套子查询，Doris 只支持子查询关联到自己的直接父查询，不支持跨层级关联到父查询的更外层查询。

关联子查询的限制​

关联的标量子查询的限制​

  • 关联条件必须是等值条件。

  • 子查询的输出必须是单个聚合函数的结果，且没有 group by 子句。

    -- 单个聚合函数，且无 group by，支持  
    select * from t1 where t1.c1 < (select max(t2.c1) from t2 where t1.c2 = t2.c2);  
      
    -- 等价改写的 SQL 如下：  
    select t1.* from t1 inner join (select t2.c2 as c2, max(t2.c1) as c1 from t2 group by t2.c2) tx on t1.c1 < tx.c1 and t1.c2 = tx.c2;  
      
    -- 非等值条件，不支持  
    select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 > t2.c2);  
      
    -- 没有聚合函数，不支持  
    select * from t1 where t1.c1 = (select t2.c1 from t2 where t1.c2 = t2.c2);  
      
    -- 有聚合函数，但包含 group by，不支持  
    select * from t1 where t1.c1 = (select max(t2.c1) from t2 where t1.c2 = t2.c2 group by t2.c2);

关联的 (not) exists 子查询的限制​

  • 子查询不能同时有offset和 limit。

    -- 带 limit 但无 offset，支持  
    select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2);  
      
    -- 等价改写 SQL 如下：  
    select * from t1 left semi join t2 on t1.c2 = t2.c2;  
      
    -- 带 offset 和 limit，不支持  
    select * from t1 where exists (select t2.c1 from t2 where t1.c2 = t2.c2 limit 2, 3);

关联的 (not) in 子查询的限制​

  • 子查询的输出必须是单个列。

  • 子查询不能带有limit。

  • 子查询不能带有聚合函数或group by子句。

    -- 支持的子查询  
    select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2);  
      
    -- 改写的等价 SQL 如下：  
    select * from t1 left semi join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2;  
      
    -- 子查询输出为多列，不支持  
    select * from t1 where (t1.a, t1.c) in (select t2.c1, t2.c from t2 where t1.c2 = t2.c2);  
      
    -- 子查询带 limit，不支持  
    select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 limit 3);  
      
    -- 带有 group by 子句，不支持  
    select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c2 = t2.c2 group by t2.c1);  
      
    -- 带有聚合函数，不支持  
    select * from t1 where t1.c1 in (select sum(t2.c1) from t2 where t1.c2 = t2.c2);

嵌套子查询的限制​

目前只支持子查询关联到自己直接的父查询，不支持关联到父查询的更外层查询。

假设还有一个t3表，其建表语句如下：

create table t3  
(  
    c1 bigint,   
    c2 bigint  
)  
DISTRIBUTED BY HASH(c1) BUCKETS 3  
PROPERTIES ("replication_num" = "1");

  • 可以支持当子查询只使用了自己直接父查询的列

    select   
        t1.c1   
    from   
        t1   
    where not exists (  
        select   
            t2.c1   
        from   
            t2   
        where not exists (  
            select   
                t3.c1   
            from   
                t3   
            where   
                t3.c2 = t2.c2  
        ) and t2.c2 = t1.c2  
    );

  • 不支持当最内层的子查询使用了直接父查询的列t2.c2，并使用了最外层查询的列t1.c1。

    select   
        t1.c1   
    from   
        t1   
    where not exists (  
        select   
            t2.c1   
        from   
            t2   
        where not exists (  
            select   
                t3.c1   
            from   
                t3   
            where   
                t3.c2 = t2.c2 and t3.c1 = t1.c1  
        )  
    );

Mark Join​

在 where 条件中，一些由 (not) in 或 (not) exists 的子查询和其他过滤条件组成的 or 关系子句，需要特殊处理才能生成正常结果。举例如下：

select 
    t1.c1, 
    t1.c2 
from t1 
where exists (
    select 
        t2.c1 
    from t2 
    where 
        t1.c2 = t2.c2
    ) or t1.c1 > 0;

这个 SQL 中的 exists 子句部分如果直接使用 left semi join，根据 left semi join 的语义，将会只输出 t1 中满足 t1.c2 = t2.c2 的行。然而，实际满足 t1.c1 > 0 这个条件的行也应该输出。为了达到这个目的，引入了 Mark Join 的机制。

备注

right semi join 类似，只是左右表不同。在这里，我们用 left semi join 作为示例。

示例 SQL 如下：

-- 此 SQL 不能实际执行，只作为演示使用  
select   
    tx.c1,   
    tx.c2   
from   
    (  
        select   
            t1.c1,   
            t1.c2,   
            mark_join_flag   
        from   
            t1 left (mark) semi join t2 on t1.c2 = t2.c2  
    ) tx  
where   
    tx.mark_join_flag or tx.c1 > 0;

Mark Join 相较于普通的 left semi join，区别在于普通的 left semi join 会直接输出左表满足条件的行，而 Mark Join 则输出原始的左表加上一个值为 true、false或 null 的标志位列（示例中的 mark_join_flag 标志）。标志位的值通过 join 条件表达式 t1.c2 = t2.c2 决定，每一行都对应一个标志位值。标志位值的计算参见下表：

t1.c2t2.c2mark_join_flag
11TRUE
12FALSE
1NULLNULL
NULL1NULL
NULLNULLNULL

有了这个标志位之后，where 过滤条件就可以改写为 where mark_join_flag or t1.c1 > 0，从而得到正确结果。

常见问题​

标量子查询必须输出单一值。若子查询返回的数据量超过一条记录，将会报告运行时错误。

对于关联的标量子查询​

在使用关联标量子查询时，如果满足关联条件的子查询返回的数据量超过一条记录，将会报告运行时错误。

请参考以下 SQL 示例：

-- 关联的标量子查询，如果t2表中满足t1.c2 = t2.c2的数据多于 1 条，则会报运行时错误
select t1.*, (select t2.c1 from t2 where t1.c2 = t2.c2) from t1;

-- 报错信息样例如下 
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT][E33] correlate scalar subquery must return only 1 row

对于非关联的标量子查询​

Doris 会在运行时添加一个assert num rows算子，如果子查询返回的数据量超过一条记录，将会报告运行时错误。

请参考以下 SQL 示例：

-- 非关联的标量子查询，如果 t2 表有多于 1 条的数据，则可能报运行时错误  
select t1.*, (select t2.c1 from t2) from t1;  

-- 报错信息样例如下  
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[CANCELLED]Expected EQ 1 to be returned by expression
Report issue
Doris 首页
下载 PDF
ASF
Resources
Community
Join the community
Copyright © 2024 The Apache Software Foundation,Licensed under the Apache License, Version 2.0. Apache, Doris, Apache Doris, the Apache feather logo and the Apache Doris logo are trademarks of The Apache Software Foundation.