如何筛选表A中符合条件的记录？验证SQL正确性及优化方案

一、原SQL语句的问题 #

原SQL存在两个核心错误：

1. 未关联key字段：需求是匹配key+date组合，但语句只关联了date，完全偏离了匹配逻辑。
2. 逻辑判断错误：原语句的逻辑是“只要B中存在任意同日期且flag=true的记录，就排除A的这条记录”，和你需要的“仅排除B中存在同key+date且flag=true的A记录”不符。比如A中key=2，date=2/2020的记录，因为B里有key=1，date=2/2020，flag=true，原SQL会错误排除这条本应保留的记录。

二、正确的查询写法 #

方法1：LEFT JOIN + 条件筛选 #

这是最直观的写法，通过左连接保留A的所有记录，再筛选符合要求的情况：

SELECT A.key, A.date
FROM A
LEFT JOIN B 
  ON A.key = B.key 
  AND A.date = B.date
WHERE 
  -- 情况1：B中存在匹配的key+date且flag为false
  (B.key IS NOT NULL AND B.flag = FALSE)
  -- 情况2：B中无匹配的key+date
  OR B.key IS NULL;

方法2：修正版NOT EXISTS #

明确关联key和date，拆分两种情况判断：

SELECT A.key, A.date
FROM A
WHERE
  -- 情况1：存在匹配的key+date且flag为false
  EXISTS (
    SELECT 1
    FROM B
    WHERE A.key = B.key 
      AND A.date = B.date 
      AND B.flag = FALSE
  )
  -- 情况2：不存在任何匹配的key+date
  OR NOT EXISTS (
    SELECT 1
    FROM B
    WHERE A.key = B.key 
      AND A.date = B.date
  );

三、使用NOT IN的写法 #

注意：仅当B.key和B.date都不为空时使用，否则NULL会导致逻辑异常：

SELECT A.key, A.date
FROM A
WHERE
  -- 情况1：key+date在B中存在且flag为false
  (A.key, A.date) IN (SELECT key, date FROM B WHERE flag = FALSE)
  -- 情况2：key+date不在B的任何记录中
  OR (A.key, A.date) NOT IN (SELECT key, date FROM B);

四、使用EXCEPT的写法 #

语法最简洁，逻辑等价于“保留A中不在B的flag=true的key+date集合里的记录”，正好覆盖需求的两种情况：

SELECT key, date FROM A
EXCEPT
SELECT key, date FROM B WHERE flag = TRUE;

五、效率对比 #

• LEFT JOIN：多数数据库优化器能很好优化，在B(key, date)有索引时效率很高。
• NOT EXISTS：性能和LEFT JOIN相当，适合处理存在NULL值的场景。
• NOT IN：存在NULL值时会有逻辑风险，数据量大时性能可能不如前两者。
• EXCEPT：语法简洁，PostgreSQL、SQL Server等数据库对其优化较好，有合适索引时效率优异。

优先推荐EXCEPT（语法简洁）或LEFT JOIN（逻辑直观），可根据数据库类型和数据量测试选择。

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