SELECT查询WHERE子句优化：排除特定条件的MXXOIS记录

需求明确 #

需排除同时满足以下两个条件的记录：

• [Instrument_Classification] 等于 'MXXOIS'
• [Price_Currency] 属于 ('CNY','BRL','CLP','THB')

需保留的记录范围：

• 所有 [Instrument_Classification] 为 NULL 的记录
• [Instrument_Classification] 为 'MXXOIS' 但 [Price_Currency] 不在上述四个货币范围内的记录
• 所有 [Instrument_Classification] 不为 'MXXOIS' 的记录

原语句错误分析 #

原WHERE子句的逻辑组合与优先级处理错误，导致：

• 错误排除了所有非'MXXOIS'且货币不在指定列表的记录
• 错误限制了NULL分类下的货币范围
• 未正确保留符合要求的'MXXOIS'记录

修正后的查询语句 #

SELECT [Instrument_Classification], [Report_Status], [Price_Currency],
        [Transaction_Reference_Number], [Venue_Transaction_ID], [Executing_Entity_ID],
        [Date Received]
FROM [Transactions].[dbo].[TradeMessage]
WHERE  [Date Received] BETWEEN '2022-10-27 00:00:00' AND '2022-10-27 23:59:59'
AND NOT (
    [Instrument_Classification] = 'MXXOIS' 
    AND [Price_Currency] IN ('CNY', 'BRL', 'CLP', 'THB')
)

逻辑说明 #

使用NOT (...)直接精准排除需过滤的组合条件，优势在于：

1. 所有不在排除规则内的记录自动被保留，无需额外枚举允许的情况
2. 无需单独处理NULL值：当[Instrument_Classification]为NULL时，[Instrument_Classification] = 'MXXOIS'不成立，括号内整体条件为假，NOT后为真，自然被保留
3. 非'MXXOIS'的记录无论货币类型，全部符合保留规则

等价正向逻辑写法（效果完全一致） #

若偏好正向枚举允许的条件，可使用以下写法：

SELECT [Instrument_Classification], [Report_Status], [Price_Currency],
        [Transaction_Reference_Number], [Venue_Transaction_ID], [Executing_Entity_ID],
        [Date Received]
FROM [Transactions].[dbo].[TradeMessage]
WHERE  [Date Received] BETWEEN '2022-10-27 00:00:00' AND '2022-10-27 23:59:59'
AND (
    [Instrument_Classification] IS NULL
    OR [Instrument_Classification] <> 'MXXOIS'
    OR ([Instrument_Classification] = 'MXXOIS' AND [Price_Currency] NOT IN ('CNY', 'BRL', 'CLP', 'THB'))
)

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