使用NOT IN的SQL查询未返回预期正确结果的问题排查

原查询有效性判断 #

你编写的SQL存在语法错误无法直接执行，且逻辑完全不符合筛选规则，不可能返回预期结果。

具体问题清单 #

• 语法层面错误
  1. 列别名引用违规：SQL执行顺序中WHERE子句早于SELECT子句执行，你在SELECT中定义的别名dept_name无法在同层WHERE中直接引用，会触发列不存在的报错。
  2. NOT IN语法不规范：NOT IN后接收值列表必须包裹在括号内，你写的tab.dept_name NOT IN 'Instant'不符合语法要求，正确写法为department NOT IN ('Instant')。
• 逻辑层面错误
  1. 条件连接符使用错误：两条筛选规则是必须同时满足的强约束，你使用OR连接两个条件，会导致只要满足任意一个条件的记录就会被返回，完全打破筛选规则。比如只要部门不是Hudson，哪怕是Instant部门、哪怕日期晚于2022-05-15，都会被查出。
  2. 全局排除规则未生效：“所有Hudson部门记录不输出”是前置全局规则，你把它放在OR的一个分支里，会导致Hudson部门的记录只要满足第二个分支条件（非Instant+日期早于2022-05-15）就会被错误返回，样例中person_number=19的Hudson记录就属于这种情况。
  3. 日期比较存在冗余：用TO_CHAR转字符串做日期比较属于不必要的操作，直接用日期类型原生比较性能更高，也能避免格式书写错误导致的匹配偏差。

修正后可直接运行的SQL #

SELECT person_number, eff_start_date, department AS dept_name
FROM tab
WHERE
  -- 全局规则：排除所有Hudson部门记录
  department <> 'Hudson'
  -- 规则1要求：排除Instant部门的记录
  AND department <> 'Instant'
  -- 规则1要求：只保留生效日期早于2022-05-15的记录
  AND eff_start_date < DATE '2022-05-15';

备注：如果你的预期结果需要包含2022-05-15当天的Replay部门记录，将日期条件中的<修改为<=即可，对应调整为eff_start_date <= DATE '2022-05-15'。

结果匹配说明 #

按你给出的样例数据，日期条件使用<时返回结果如下：

如果把日期条件改成<=，会多返回person_number=28的Replay部门记录，覆盖你提到的所有预期部门范围。

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