如何在SELECT CASE表达式中使用EXISTS检查列中值是否存在

我需要从数据库中查询所有人员的姓名，并用BIT字段标记该人员是否至少拥有一份水管工（plumber）工作。我最初写的查询语句如下：

SELECT p.Name,
       CASE
           WHEN EXISTS (select 1 FROM j WHERE j.Name = 'plumber') THEN 1
           ELSE 0
           END IsPlumber
FROM People p INNER JOIN Jobs j
     ON j.personId = p.Id
GROUP BY p.Name

执行时报错：Invalid object name 'j'。我知道可以通过COUNT的方式实现：

...
    CASE
        WHEN COUNT(
            CASE
                WHEN j.Name = 'plumber' THEN 1
                END) > 0 THEN 1
        ELSE 0
    END IsPlumber
...

但听说COUNT的效率较低，所以希望能让第一种用EXISTS的方案可行，请问该怎么调整？

你报错的核心原因是：EXISTS子查询里的FROM j是试图引用一个名为j的独立表，而非外层JOIN语句里的Jobs表别名。子查询无法直接复用外层的表别名作为FROM的数据源，必须明确关联外层的人员ID来限定判断范围。

这里提供两种可行的调整方案：

方案一：移除外层JOIN，直接通过EXISTS关联人员ID #

这种方式无需JOIN Jobs表，直接通过EXISTS判断当前人员是否有水管工工作，避免了GROUP BY的额外开销，性能更优：

SELECT 
    p.Name,
    CASE
        WHEN EXISTS (
            SELECT 1 
            FROM Jobs j 
            WHERE j.personId = p.Id AND j.Name = 'plumber'
        ) THEN 1
        ELSE 0
    END IsPlumber
FROM People p

方案二：保留外层JOIN，调整EXISTS的关联逻辑 #

如果因其他需求必须保留JOIN Jobs的结构，需要给子查询里的Jobs表设置不同别名（避免和外层别名冲突），同时通过personId关联到当前人员：

SELECT 
    p.Name,
    CASE
        WHEN EXISTS (
            SELECT 1 
            FROM Jobs j_inner 
            WHERE j_inner.personId = p.Id AND j_inner.Name = 'plumber'
        ) THEN 1
        ELSE 0
    END IsPlumber
FROM People p 
INNER JOIN Jobs j 
    ON j.personId = p.Id
GROUP BY p.Name

补充说明 #

• 方案一无需GROUP BY，直接从People表获取所有人员，逻辑更简洁，性能表现更好，推荐优先使用。
• 方案二保留了原有JOIN结构，但必须通过不同别名区分内层和外层的Jobs表，确保子查询只针对当前人员的工作情况做判断。

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