如何删除表中在另一表已存在的行 自写SQL语句是否有疏漏？

你的SQL存在的疏漏： #

• 子查询返回多行时会直接报错：你用了=来比对子查询的返回结果，只要position表中存在多条满足t.column1 = p.column1 AND t.column2 = p.column2的行，子查询会返回多个p.employee值，等于比较无法处理多值结果，SQL会直接执行失败。
• 匹配逻辑和需求不符：你额外加了t.employee = p.employee的判断，如果你判定「tmp表行在position表已存在」的规则是column1+column2两个字段相等即可，那这条额外判断会导致很多应该被删除的行没有被删除，逻辑不符合你的原始需求。
• 执行效率低：这种相关子查询会对tmp表的每一行都执行一次子查询匹配，两张表数据量大的时候性能会非常差。

优化后的正确写法 #

写法1：使用EXISTS（兼容所有标准SQL数据库） #

如果你判定行存在的规则是column1+column2相等：

DELETE FROM tmp t
WHERE EXISTS (
    SELECT 1 FROM position p
    WHERE t.column1 = p.column1 
      AND t.column2 = p.column2
);

如果你的存在判定规则需要加上employee字段一起匹配，只需要在子查询里加对应的条件即可：

DELETE FROM tmp t
WHERE EXISTS (
    SELECT 1 FROM position p
    WHERE t.column1 = p.column1 
      AND t.column2 = p.column2
      AND t.employee = p.employee
);

写法2：关联删除（不同数据库语法略有差异，以下为MySQL示例） #

DELETE t FROM tmp t
INNER JOIN position p 
  ON t.column1 = p.column1 
  AND t.column2 = p.column2;

注意事项 #

删除数据前务必先执行SELECT语句验证待删除的数据是否正确，避免误删：

-- 先查询确认要删除的行
SELECT * FROM tmp t
WHERE EXISTS (
    SELECT 1 FROM position p
    WHERE t.column1 = p.column1 
      AND t.column2 = p.column2
);

有条件的话建议先开启事务，执行删除后确认数据无误再提交，出现问题可以直接回滚。

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