T-SQL: 通过连接选择要删除的行

Question

场景：

假设我有两个表格，TableA和TableB。 TableB的主键是一个单列（BId），并且是TableA中的外键列。

在我的情况下，我想删除所有与TableB中特定行相关联的TableA行：我可以通过连接来完成吗？删除从连接中提取的所有行？

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

还是说我必须这样做：

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

我提出这个问题是因为在处理更大的表时，第一个选项似乎更加高效。

谢谢！

- John

Answer 1

DELETE TableA
FROM   TableA a
       INNER JOIN TableB b
               ON b.Bid = a.Bid
                  AND [my filter condition] 

应该可以工作

- TheTXI

---

我在连接操作中使用了 And [我的筛选条件]，而不是 Where 子句。我想两者都可以工作，但是在连接操作中使用筛选条件将限制您从连接操作中获取的结果。 - TheTXI

为什么我们需要写成“DELETE TableA FROM”而不是“DELETE FROM”？我发现只有这种情况才能正常工作，但是为什么呢？ - UnDiUdin

我认为这是因为你需要指明要从哪个表中删除记录。我刚刚使用了DELETE TableA, TableB ...的语法来运行一个查询，这实际上删除了两个表中的相关记录。不错。 - Andrew

在PostgreSQL语法中，使用join不起作用，但可以使用"using"关键字。DELETE from TableA a using TableB b where b.Bid = a.Bid and [我的筛选条件] - bartolo-otrit

在MySQL中，您会收到一个错误“MULTI DELETE中的未知表' TableA'”，这是因为您为TableA（a）声明了一个别名。小调整：DELETE a FROM TableA a INNER JOIN TableB b on b.Bid = a.Bid and [my filter condition] - masam

Answer 2

我会使用这种语法

Delete a 
from TableA a
Inner Join TableB b
on  a.BId = b.BId
WHERE [filter condition]

- cmsjr

---

我也更喜欢这种语法，从逻辑上讲更容易理解正在发生的事情。另外，我知道你可以在更新操作中使用同样的语法。 - Adam Nofsinger

我也更喜欢这种方式，因为在DELETE之后放置表别名似乎更符合我的直觉，更清楚地表示正在被删除的是什么。 - Jagd

我也更喜欢这个。特别是在需要实际连接同一个表的情况下（例如删除重复记录）。这种情况下，我需要为我要删除的“一侧”使用别名，并且这种语法使得清楚明了，我正在从重复的别名中进行删除。 - Chris Simmons

Answer 3

可以的，以下是示例：

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

- Diadistis

---

我更愿意使用别名来指代第一行的表格。也就是说，使用“Delete a”而不是“Delete TableA”。如果你将表格与自身连接，这样做可以清楚地显示你想要删除哪一侧。 - Jeremy Stein

Answer 4

我试图在Access数据库中进行删除操作，发现需要在delete后使用a.*。

DELETE a.*
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

- Tony Emrud

---

从被拒绝的待处理编辑中： “UniqueRecords属性必须设置为是，否则它将无法工作。（https://support.microsoft.com/kb/240098）” - StuperUser

Answer 5

在MySQL中，操作几乎相同，但是您必须在单词“DELETE”后立即使用表别名：

DELETE a
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

- Michael Butler

Answer 6

我正在使用这个

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]

@TheTXI的方法已经足够好了，但是我阅读了答案和评论，发现必须回答的一件事情是在WHERE子句或作为连接条件中使用条件。所以我决定测试并编写代码片段，但是没有发现它们之间有什么实质性的区别。你可以在这里看到SQL脚本，重要的一点是我更喜欢将其写成注释，因为这不是确切的答案，但它很大，不能放在注释中，请谅解。

Declare @TableA  Table
(
  aId INT,
  aName VARCHAR(50),
  bId INT
)
Declare @TableB  Table
(
  bId INT,
  bName VARCHAR(50)  
)

Declare @TableC  Table
(
  cId INT,
  cName VARCHAR(50),
  dId INT
)
Declare @TableD  Table
(
  dId INT,
  dName VARCHAR(50)  
)

DECLARE @StartTime DATETIME;
SELECT @startTime = GETDATE();

DECLARE @i INT;

SET @i = 1;

WHILE @i < 1000000
BEGIN
  INSERT INTO @TableB VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableA VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE a
--SELECT *
FROM @TableA a
Inner Join @TableB b
ON  a.BId = b.BId
WHERE a.aName LIKE '%5'

SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())

SET @i = 1;
WHILE @i < 1000000
BEGIN
  INSERT INTO @TableD VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableC VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE c
--SELECT *
FROM @TableC c
Inner Join @TableD d
ON  c.DId = d.DId
AND c.cName LIKE '%5'

SELECT Duration    = DATEDIFF(ms,@StartTime,GETDATE())

如果您能从这个脚本中得到好的理由或编写另一个有用的脚本，请分享。谢谢，希望这可以帮助您。

- QMaster

Answer 7

上述语法在Interbase 2007中无法使用。相反，我需要使用类似以下内容：

DELETE FROM TableA a WHERE [filter condition on TableA] 
  AND (a.BId IN (SELECT a.BId FROM TableB b JOIN TableA a 
                 ON a.BId = b.BId 
                 WHERE [filter condition on TableB]))

（注意，Interbase不支持AS关键字用于别名）

- DavidJ

Answer 8

在SQLite中，唯一有效的方法类似于beauXjames的答案。

似乎归结为以下内容： DELETE FROM table1 WHERE table1.col1 IN (SOME TEMPORARY TABLE); 可以通过SELECT和JOIN两个表来创建一些临时表，您可以根据要删除Table1中记录的条件过滤此临时表。

- Bhoom Suktitipat

Answer 9

假设你有两个表，一个是主表（例如：员工），另一个是子表（例如：家属），你想要删除所有在子表中无法与主表中的任何行匹配的数据行。

delete from Dependents where EmpID in (
select d.EmpID from Employees e 
    right join Dependents d on e.EmpID = d.EmpID
    where e.EmpID is null)

这里需要注意的是，你只是从连接中收集了一个“EmpIDs”数组，然后使用该组EmpIDs在“Dependents”表上执行删除操作。

- beauXjames

Answer 10

您可以运行此查询：

    DELETE FROM TableA
    FROM
       TableA a, TableB b 
    WHERE
       a.Bid=b.Bid
    AND
       [my filter condition]

- Aditya