如何使用CASE WHEN EXISTS实现前置条件触发的SQL查询？

需求说明 #

需要实现这样的SQL逻辑：先检查是否存在符合第一个查询条件的记录，若存在则返回第二个查询的结果；若不存在，返回提示信息。

检查用查询 #

SELECT * FROM Question  
WHERE question_id = 1 
      AND Type = 'FirstPage'  
      AND Question= 'page1_question' 
      AND Answer = 'page1_answer'

目标返回查询 #

SELECT * FROM Question  
WHERE Question_id = 1 
      AND Type = 'FirstPage' 
      AND Question='Page1_question_checkbox' 
      AND Answer = 'page1_answer_checkbox'

原尝试SQL的问题 #

你写的CASE语句无法运行，原因是：CASE要求THEN和ELSE分支返回结构完全一致的结果。你的THEN分支返回的是Question表的多列结果集，而ELSE分支返回的是单一字符串，两者类型不匹配，导致数据库无法解析。

正确实现方案 #

方案1：用IF EXISTS分支执行（适用于MySQL、SQL Server等） #

这种写法逻辑清晰，直接分条件执行不同查询：

IF EXISTS (
    SELECT * FROM Question  
    WHERE question_id = 1 
          AND Type = 'FirstPage'  
          AND Question= 'page1_question' 
          AND Answer = 'page1_answer'
)
BEGIN
    -- 满足条件时返回目标查询结果
    SELECT * FROM Question  
    WHERE Question_id = 1 
          AND Type = 'FirstPage' 
          AND Question='Page1_question_checkbox' 
          AND Answer = 'page1_answer_checkbox';
END
ELSE
BEGIN
    -- 不满足时返回提示
    SELECT 'Condition is not met.' AS Result;
END

方案2：用UNION ALL实现通用兼容写法（支持多数数据库） #

如果需要跨数据库兼容，可以用UNION ALL，注意保证两个分支的列数和数据类型一致：

-- 满足条件时返回目标记录
SELECT 
    question_id,
    Type,
    Question,
    Answer,
    NULL AS Message
FROM Question
WHERE EXISTS (
    SELECT * FROM Question  
    WHERE question_id = 1 
          AND Type = 'FirstPage'  
          AND Question= 'page1_question' 
          AND Answer = 'page1_answer'
)
AND question_id = 1 
AND Type = 'FirstPage' 
AND Question='Page1_question_checkbox' 
AND Answer = 'page1_answer_checkbox'

UNION ALL

-- 不满足条件时返回提示，其他列补NULL
SELECT 
    NULL AS question_id,
    NULL AS Type,
    NULL AS Question,
    NULL AS Answer,
    'Condition is not met.' AS Message
WHERE NOT EXISTS (
    SELECT * FROM Question  
    WHERE question_id = 1 
          AND Type = 'FirstPage'  
          AND Question= 'page1_question' 
          AND Answer = 'page1_answer'
);

方案3：仅返回单一结果字符串（如果不需要完整表列） #

如果只需要返回文本类结果，调整CASE分支让结构一致：

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT * FROM Question  
            WHERE question_id = 1 
                  AND Type = 'FirstPage'  
                  AND Question= 'page1_question' 
                  AND Answer = 'page1_answer'
        ) THEN (
            SELECT CONCAT('找到匹配记录：', Question, ' - ', Answer) 
            FROM Question  
            WHERE Question_id = 1 
                  AND Type = 'FirstPage' 
                  AND Question='Page1_question_checkbox' 
                  AND Answer = 'page1_answer_checkbox'
        )
        ELSE 'Condition is not met.'
    END AS Result;

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