SQL WHERE子句默认参数设置：如何实现‘全部’选项返回所有行？

问题描述 #

我想编写一段根据用户输入筛选数据行的SQL，其中包含“选择全部行”的选项。现有示例SQL中，选择'M'或'F'时能得到正确结果，但选择'ALL'时没有数据返回，请问该如何修改SQL，让选择'ALL'时返回所有记录？

原示例代码：

WITH    CTE_PROMPT AS (
        SELECT  'M' Gender
        ),  
        CTE_DATA AS (
        SELECT  PERSON_NUMBER
                ,SEX                
        FROM    Employees  
        )
        
SELECT  CTE_DATA.*
FROM    CTE_DATA
        LEFT OUTER JOIN CTE_PROMPT ON CTE_DATA.Sex = CTE_PROMPT.Gender
WHERE   CASE 
            WHEN Gender = 'ALL' THEN 1
            WHEN Gender = SEX THEN 1
            ELSE 0
        END = 1

问题原因 #

原SQL的核心问题是错误使用了LEFT JOIN关联筛选条件：当CTE_PROMPT中的Gender为'ALL'时，CTE_DATA.Sex = 'ALL'的匹配条件无法命中员工表中的有效记录，导致CTE_PROMPT.Gender在结果集中为NULL。后续CASE判断无法触发WHEN Gender = 'ALL'的分支，最终CASE返回0，不满足WHERE条件，因此无数据返回。

修改方案 #

方案一：使用交叉连接+OR条件 #

通过CROSS JOIN获取筛选条件，用OR逻辑区分“全部选择”和“特定性别选择”的场景，逻辑更直观：

WITH    CTE_PROMPT AS (
        -- 替换为用户输入的Gender值：'ALL'/'M'/'F'
        SELECT  'ALL' Gender
        ),  
        CTE_DATA AS (
        SELECT  PERSON_NUMBER
                ,SEX                
        FROM    Employees  
        )
        
SELECT  CTE_DATA.*
FROM    CTE_DATA
CROSS JOIN CTE_PROMPT
WHERE   CTE_PROMPT.Gender = 'ALL' -- 选择全部时跳过性别筛选
        OR CTE_DATA.SEX = CTE_PROMPT.Gender -- 选择特定性别时匹配

方案二：直接嵌入筛选条件（更简洁） #

无需额外的CTE_DATA，直接在WHERE子句中判断筛选条件：

WITH    CTE_PROMPT AS (
        SELECT  'ALL' Gender
        )
SELECT  PERSON_NUMBER, SEX
FROM    Employees
WHERE   (SELECT Gender FROM CTE_PROMPT) = 'ALL'
        OR SEX = (SELECT Gender FROM CTE_PROMPT)

效果说明 #

• 当CTE_PROMPT中的Gender设为'ALL'时，WHERE的第一个条件成立，直接返回员工表所有记录；
• 当Gender设为'M'或'F'时，第二个条件生效，精准筛选出对应性别的员工数据。

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