Correlated subqueries-AnalyticDB(AnalyticDB)-阿里云帮助中心

A correlated subquery references a column from its outer query and runs once for every row the outer query processes. This can degrade performance significantly on large datasets.

AnalyticDB for MySQL supports correlated subqueries with specific constraints. Not every standard SQL form is supported.

When possible, rewrite correlated subqueries as JOIN statements for better performance.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster running version V3.1.9.0 or later

To check or update the minor version, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page. For instructions, see Update the minor version of a cluster.

How it works

AnalyticDB for MySQL automatically decorrelates a correlated subquery into a common subquery during query planning. If the subquery uses a pattern that prevents decorrelation—such as referencing a correlated column in the SELECT clause—the query fails with an error.

Limitations

Limitations on correlated columns

A correlated column is a column in the subquery that comes from the outer query.

Correlated columns can only appear in the WHERE clause of a subquery.

Correlated columns cannot appear in a subquery that is nested inside another subquery:

-- Not supported: correlated column ts1.b in a doubly-nested subquery
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));

Limitations on subqueries

A subquery cannot appear inside a JOIN statement.
A subquery cannot contain window functions.

A subquery cannot contain set operations such as UNION:

-- Not supported: UNION inside a correlated subquery
SELECT * FROM ts1 WHERE ts1.a IN
    (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b UNION SELECT ts3.a FROM ts3);

A subquery used in an IN clause cannot contain LIMIT, GROUP BY, ORDER BY, or JOIN:

-- Not supported: LIMIT inside an IN subquery
SELECT * FROM ts1 WHERE ts1.a IN
    (SELECT ts2.a FROM ts2 WHERE ts1.b = ts2.b LIMIT 10);

-- Not supported: GROUP BY inside an IN subquery
SELECT * FROM ts1 WHERE ts1.a IN
    (SELECT AVG(ts2.a) FROM ts2 WHERE ts1.b = ts2.b GROUP BY ts2.c);

Example

The following example queries the minimum discount for each product using a correlated subquery, then rewrites it as a JOIN for better performance.

Correlated subquery:

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

Rewritten as a JOIN (recommended):

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;

The JOIN version computes the minimum discount for all products in a single pass, which performs better than running a subquery for each row in goods.

Troubleshooting

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

This error appears when a correlated column is referenced in the subquery's SELECT clause. For example, when ts1.a appears in the SELECT clause, the error message reads:

Given correlated subquery with correlation: ts1.a is not supported

Cause: Correlated columns can only appear in the WHERE clause. When decorrelation fails because a correlated column is in the SELECT clause, AnalyticDB for MySQL returns this error.

The following query triggers this error because ts1.a is used inside the subquery's SELECT clause:

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

Solution:

Option 1: Move the correlated column out of the subquery's SELECT clause:

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

Option 2: Rewrite the query as a 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"

Cause: If a correlated subquery returns multiple rows, an error occurs. If a correlated subquery does not return a row or returns only one row, no error occurs.

The following query triggers this error when the subquery returns multiple matching rows because an equality condition is used in the outer query:

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

Solution: Replace the equality condition with IN:

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