关联子查询

关联子查询是一种特殊的子查询,子查询的执行依赖外部查询的当前行。关联子查询的外部查询每查询一行数据,子查询都需要运行一次,在数据量大的情况下,可能会导致查询性能下降。本文主要介绍关联子查询的用法及常见报错。

前提条件

集群内核版本需为3.1.9.0及以上版本。

说明 如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

使用限制

关联列的限制

说明

关联列是指子查询中包含的来自外部查询的列。

  • 关联列仅允许出现在子查询的WHERE子句中。

  • 关联子查询不支持跳层关联,即不支持在子查询包含的子查询中出现关联列。例如:

    SELECT * FROM ts1 WHERE ts1.a > 
        (SELECT MAX(ts3.c) FROM ts3 WHERE ts3.c IN 
            (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b ) );

子查询的限制

  • 子查询不能位于JOIN语句中。

  • 子查询中不能包含任意窗口函数。

  • 子查询不能包含任何的集合操作。例如UNION等。例如:

    SELECT * FROM ts1 WHERE ts1.a IN 
        (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b UNION SELECT ts3.a FROM ts3 );
  • IN条件的子查询不能包含LIMIT、GROUP BY、ORDER BY、JOIN。例如:

    SELECT * FROM ts1 WHERE ts1.a IN 
        (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b LIMIT 10);
    SELECT * FROM ts1 WHERE ts1.a IN 
        (SELECT AVG(ts2.a) FROM ts2 WHERE ts1.b = ts2.b GROUP BY ts2.c );

示例

使用关联子查询,查询商品的最低折扣:

SELECT id,  
    (SELECT MIN(discount)
    FROM item
    WHERE goods.id = goods_id )
FROM goods;

您也可以通过JOIN改写上述关联子查询:

SELECT id,t.min_discount
FROM goods
LEFT JOIN 
    (SELECT goods_id,
         MIN(discount) AS min_discount
    FROM item
    GROUP BY  goods_id ) t
    ON t.goods_id = goods.id;

常见报错

Given correlated subquery with correlation: [column_name] is not supported

原因:关联列仅允许出现在WHERE子句中。AnalyticDB for MySQL在执行关联子查询时,会解关联,即将关联子查询变为普通子查询,若关联列出现在子查询的SELECT子句中,会解关联失败,进而导致报错。

以如下代码为例,关联列ts1.a出现在子查询的SELECT子句中,导致关联查询无法解开,出现报错。

SELECT ts1.a,
    (SELECT SUM(ts2.a) + ts1.a
    FROM ts2
    WHERE ts1.b = ts2.b )
FROM ts1;

解决方法:

  • 方法一:在子查询的SELECT子句中去除关联列,改写SQL。

    SELECT ts1.a,    
        (SELECT SUM(ts2.a)
        FROM ts2
        WHERE ts1.b = ts2.b ) + ts1.a
    FROM ts1;
  • 方法二:通过JOIN改写关联子查询。

    SELECT ts1.a,
             tt.suma + ts1.a
    FROM ts1
    LEFT JOIN 
        (SELECT ts2.b AS b,
             SUM(ts2.a) AS suma
        FROM ts2
        GROUP BY  ts2.b ) tt
        ON ts1.b = tt.b;

Scalar sub-query has returned multiple rows

原因:关联子查询返回结果有多行时,会出现该报错,若返回结果行数等于0或1时,不会报错。

以如下代码为例,外部查询的WHERE子句中是等值条件,且子查询的返回结果为多行,出现报错。

SELECT ts1.a
FROM ts1
WHERE ts1.a = 
    (SELECT ts2.a
    FROM ts2
    WHERE ts1.b = ts2.b );

解决方法:根据SQL语义修改SQL。根据上述查询中的语义,将外部查询的等值条件修改为IN条件。

SELECT ts1.a
FROM ts1
WHERE ts1.a IN 
    (SELECT ts2.a
    FROM ts2
    WHERE ts1.b = ts2.b );