WHERE子句顺序是否重要？查询结果与Excel过滤计数差异排查

核心原因：NOT IN 对 NULL 值的处理逻辑差异 #

你遇到的记录数过少问题，核心是sta字段存在NULL值导致的逻辑冲突：

• 在SQL中，NOT IN ('LP','SOD')会把sta为NULL的行直接过滤——因为NULL NOT IN ('LP','SOD')的结果是UNKNOWN，而WHERE子句仅保留判断结果为TRUE的记录。
• 但Excel中过滤“不等于LP且不等于SOD”时，会默认保留空白（对应SQL的NULL）的行，这就造成了两者的计数差异。

验证步骤 #

执行以下查询确认sta字段的NULL数量：

SELECT COUNT(*) AS null_sta_count
FROM mytable
WHERE sta IS NULL
  AND prod IN (1, 22, 3, 45, 354, 951, 10)
  AND mydte2 between 'yyyy/mm/dd' and 'yyyy/mm/dd';

如果结果大于0，即可确认NULL值是差异来源。

解决方案 #

方案1：匹配Excel逻辑（保留NULL行） #

修改WHERE条件，明确包含NULL的情况：

SELECT myfield2, myacc, mydate, mydte2,
     ROW_NUMBER() OVER (Partition BY myacc, EXTRACT(YEAR FROM mydate),EXTRACT(MONTH FROM mydate) ORDER BY mydate) as RowN 
FROM mytable
WHERE prod IN (1, 22, 3, 45, 354, 951, 10)
  AND (sta NOT IN ('LP','SOD') OR sta IS NULL)
  AND mydte2 between 'yyyy/mm/dd' and 'yyyy/mm/dd'
QUALIFY ROW_NUMBER() OVER (Partition By myacc ORDER BY mydate) = 1;

（注：原查询中SELECT重复写了myfield2，已去掉冗余字段）

方案2：用NOT EXISTS规避NULL陷阱 #

NOT EXISTS对NULL的处理更直观，不会出现意外过滤：

SELECT myfield2, myacc, mydate, mydte2,
     ROW_NUMBER() OVER (Partition BY myacc, EXTRACT(YEAR FROM mydate),EXTRACT(MONTH FROM mydate) ORDER BY mydate) as RowN 
FROM mytable t1
WHERE prod IN (1, 22, 3, 45, 354, 951, 10)
  AND NOT EXISTS (
      SELECT 1 
      FROM (VALUES ('LP'), ('SOD')) AS t2(val)
      WHERE t1.sta = t2.val
  )
  AND mydte2 between 'yyyy/mm/dd' and 'yyyy/mm/dd'
QUALIFY ROW_NUMBER() OVER (Partition By myacc ORDER BY mydate) = 1;

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