SQL查询comm_group全为NULL用户完整行，如何解决列歧义报错

问题原因 #

你遇到的歧义报错是因为NOT IN子查询中复用了外层的A别名，数据库无法区分字段引用的是外层还是子查询的表，修改子查询别名即可解决。

方案1：修改原NOT IN写法（最小改动） #

直接给子查询的表设置独立别名，就可以正常查询所有列：

WITH A AS (long query)
SELECT A.*
FROM A
WHERE A.USER_ID NOT IN (
    SELECT sub.USER_ID 
    FROM A sub 
    WHERE sub.COMM_GROUP IS NOT NULL
)

方案2：窗口函数实现（性能更优） #

用窗口函数按用户维度聚合判断，只需扫描一次CTE结果，数据量大时效率更高：

WITH A AS (long query)
SELECT *
FROM (
    SELECT 
        *,
        MAX(COMM_GROUP) OVER (PARTITION BY USER_ID) AS user_max_group
    FROM A
) t
WHERE user_max_group IS NULL

如果某个用户下存在任意非空的COMM_GROUP，MAX(COMM_GROUP)就会返回非空值，过滤后剩下的就是所有COMM_GROUP全为NULL的用户的完整行。

方案3：NOT EXISTS写法（更稳定） #

如果USER_ID存在为空的可能性，NOT IN会返回异常结果，改用NOT EXISTS可以规避这个问题：

WITH A AS (long query)
SELECT A.*
FROM A
WHERE NOT EXISTS (
    SELECT 1 
    FROM A sub 
    WHERE sub.USER_ID = A.USER_ID 
    AND sub.COMM_GROUP IS NOT NULL
)

内容的提问来源于stack exchange，提问作者Charlie Patton