Correlated subqueries - AnalyticDB - Alibaba Cloud Documentation Center

A correlated subquery depends on the current row of an outer query. The correlated subquery is executed once for each row that is processed by the outer query. If a large amount of data is involved, the query performance degrades. This topic describes the usage and common errors of correlated subqueries.

Prerequisites

An AnalyticDB for MySQL cluster of V3.1.9.0 or later is created.

Note

For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

Limits

Limits on correlated columns

Note

A correlated column in the subquery comes from an outer query.

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

Correlated columns cannot be referenced in a subquery that is contained in another subquery. Example:

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 ) );

Limits on subqueries

A subquery cannot be contained in a JOIN statement.
A subquery cannot contain window functions.

A subquery cannot contain set operations, such as UNION. Example:

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 in an IN clause cannot contain LIMIT, GROUP BY, ORDER BY, or JOIN. Examples:

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 );

Examples

Use a correlated subquery to query the minimum discount of a product.

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

Use a JOIN clause to rewrite the preceding correlated subquery.

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;

Common errors

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

Cause: Correlated columns can be referenced only in the WHERE clause of a subquery. When a correlated subquery is used, AnalyticDB for MySQL decorrelates the correlated subquery to a common subquery. If a correlated column is referenced in the SELECT clause of the subquery, the decorrelation fails and an error occurs.

In the following sample statement, a correlated column named ts1.a is referenced in the SELECT clause of the subquery. In this case, the decorrelation fails and an error occurs.

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

Solution:

Method 1: Remove the correlated column from the SELECT clause of the subquery.

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

Method 2: Use a JOIN clause to rewrite the correlated subquery.

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.

In the following sample statement, an equality condition is used in the WHERE clause of the outer query and the correlated subquery returns multiple rows. In this case, an error occurs.

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

Solution: Change the equality condition in the outer query to the IN condition based on the preceding SQL syntax.

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