如何无需指定所有外键关系查找SQL Server孤立行？

一、检索所有孤立行 #

假设Files表的主键为FileId，请将以下示例中的关联表替换为你实际存在外键引用的表，确保覆盖所有关联Files的表：

方法1：NOT EXISTS（性能更优） #

SELECT f.*
FROM Files f
WHERE NOT EXISTS (SELECT 1 FROM DocumentAttachments da WHERE da.FileId = f.FileId)
  AND NOT EXISTS (SELECT 1 FROM UserUploads uu WHERE uu.FileId = f.FileId)
  AND NOT EXISTS (SELECT 1 FROM ProjectFiles pf WHERE pf.FileId = f.FileId);

方法2：LEFT JOIN + IS NULL #

SELECT f.*
FROM Files f
LEFT JOIN DocumentAttachments da ON da.FileId = f.FileId
LEFT JOIN UserUploads uu ON uu.FileId = f.FileId
LEFT JOIN ProjectFiles pf ON pf.FileId = f.FileId
WHERE da.FileId IS NULL
  AND uu.FileId IS NULL
  AND pf.FileId IS NULL;

二、批量删除孤立行 #

确认检索结果无误后，可使用以下语句批量删除：

方法1：直接删除（基于NOT EXISTS） #

DELETE f
FROM Files f
WHERE NOT EXISTS (SELECT 1 FROM DocumentAttachments da WHERE da.FileId = f.FileId)
  AND NOT EXISTS (SELECT 1 FROM UserUploads uu WHERE uu.FileId = f.FileId)
  AND NOT EXISTS (SELECT 1 FROM ProjectFiles pf WHERE pf.FileId = f.FileId);

方法2：CTE筛选后删除（更灵活） #

WITH OrphanedFiles AS (
    SELECT f.FileId
    FROM Files f
    WHERE NOT EXISTS (SELECT 1 FROM DocumentAttachments da WHERE da.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM UserUploads uu WHERE uu.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM ProjectFiles pf WHERE pf.FileId = f.FileId)
)
DELETE FROM Files
WHERE FileId IN (SELECT FileId FROM OrphanedFiles);

大表分批删除（避免锁表） #

如果Files表数据量极大，建议分批删除，避免长时间锁表影响业务：

WHILE EXISTS (
    SELECT 1 FROM Files f
    WHERE NOT EXISTS (SELECT 1 FROM DocumentAttachments da WHERE da.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM UserUploads uu WHERE uu.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM ProjectFiles pf WHERE pf.FileId = f.FileId)
)
BEGIN
    DELETE TOP(1000) f
    FROM Files f
    WHERE NOT EXISTS (SELECT 1 FROM DocumentAttachments da WHERE da.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM UserUploads uu WHERE uu.FileId = f.FileId)
      AND NOT EXISTS (SELECT 1 FROM ProjectFiles pf WHERE pf.FileId = f.FileId)
END

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