SQL Server中CTE搭配IN使用报错原因及JOIN可行性咨询

第一段查询的错误 #

第一段SQL的核心错误在WHERE E.assoc_id IN (Result)这一行：

• *CTE（Common Table Expression，即这里的Result）*是一个临时结果集，本质是一张临时表，不能直接作为IN操作符的参数。IN要求后面跟具体值列表或者返回单列结果的子查询，正确写法应该是E.assoc_id IN (SELECT assoc_id FROM Result)。

能否用JOIN查询CTE结果？ #

当然可以，而且用JOIN关联CTE是比IN更推荐的写法（数据量较大时，JOIN的执行性能通常更稳定）。你可以把第一段代码改成JOIN的形式，示例如下：

WITH Result AS
(
    SELECT
        A.assoc_id  
    FROM
        Employee A 
    WHERE 
        assoc_id NOT IN (SELECT B.assoc_ref_id FROM bonus B) 
)
SELECT
    E.firstname, E.lastname, T.assoc_title 
FROM
    Employee E 
INNER JOIN
    Title T ON E.assoc_id = T.assoc_ref_id
INNER JOIN
    Result R ON E.assoc_id = R.assoc_id -- 通过JOIN关联CTE筛选目标员工

额外提醒：使用NOT IN时要注意，如果bonus表的assoc_ref_id字段存在NULL值，NOT IN会直接返回空结果，这时候改用NOT EXISTS会更安全可靠：

WITH Result AS
(
    SELECT
        A.assoc_id  
    FROM
        Employee A 
    WHERE 
        NOT EXISTS (SELECT 1 FROM bonus B WHERE B.assoc_ref_id = A.assoc_id)
)
SELECT
    E.firstname, E.lastname, T.assoc_title 
FROM
    Employee E 
INNER JOIN
    Title T ON E.assoc_id = T.assoc_ref_id
INNER JOIN
    Result R ON E.assoc_id = R.assoc_id

内容的提问来源于stack exchange，提问作者Abhishek Singh Rana