Amazon Redshift中NOT EXISTS关联子查询报错及需求实现求助

你在Amazon Redshift中执行NOT EXISTS关联子查询时收到错误：

This type of correlated subquery pattern is not supported due to internal error

这是因为Redshift对特定类型的关联子查询模式支持有限，这种引用外层表pkey的NOT EXISTS子查询属于Redshift内部暂不支持的场景。你的需求是：排除所有对应pkey在Table2中存在src='C'的记录，同时保留Table1和Table2中src为'A'/'B'的其他详情。

替代方案1：LEFT JOIN + IS NULL #

用左连接替代NOT EXISTS逻辑，绕过Redshift的子查询限制：

SELECT t1.*, t2.ipkey, t2.src
FROM table1 t1
INNER JOIN table2 t2 ON t1.pkey = t2.pkey
LEFT JOIN table2 t2i ON t1.pkey = t2i.pkey AND t2i.src = 'C'
WHERE t2.src IN ('A', 'B')
AND t2i.pkey IS NULL;

替代方案2：先筛选有效pkey再关联 #

先找出不存在src='C'的pkey集合，再关联两张表获取详情，逻辑更直观：

WITH valid_pkeys AS (
    SELECT DISTINCT pkey
    FROM table2
    WHERE pkey NOT IN (SELECT pkey FROM table2 WHERE src = 'C')
)
SELECT t1.*, t2.ipkey, t2.src
FROM table1 t1
INNER JOIN valid_pkeys vp ON t1.pkey = vp.pkey
INNER JOIN table2 t2 ON t1.pkey = t2.pkey
WHERE t2.src IN ('A', 'B');

预期结果 #

以上两种方案均可得到你需要的结果集：

内容的提问来源于stack exchange，提问作者John Smith