SQL 中 DELETE 和 DELETE FROM 的区别? [英] Difference between DELETE and DELETE FROM in SQL?

问题描述

有吗?我正在研究一些存储过程，在一个地方我发现了以下行:

Is there one? I am researching some stored procedures, and in one place I found the following line:

DELETE BI_Appointments
WHERE VisitType != (
    SELECT TOP 1 CheckupType
    FROM BI_Settings
    WHERE DoctorName = @DoctorName)

这会做同样的事情:

DELETE FROM BI_Appointments
WHERE VisitType != (
    SELECT TOP 1 CheckupType
    FROM BI_Settings
    WHERE DoctorName = @DoctorName)

或者是语法错误，还是完全不同的东西?

Or is it a syntax error, or something entirely different?

推荐答案

假设这是 T-SQL 或 MS SQL Server，没有区别，语句相同.在 DELETE 语句中，第一个 FROM 关键字在语法上是可选的.

Assuming this is T-SQL or MS SQL Server, there is no difference and the statements are identical. The first FROM keyword is syntactically optional in a DELETE statement.

http://technet.microsoft.com/en-us/library/ms189835.aspx

关键字是可选的，原因有两个.

The keyword is optional for two reasons.

首先，标准要求在子句中使用 FROM 关键字，因此它必须存在以符合标准.

First, the standard requires the FROM keyword in the clause, so it would have to be there for standards compliance.

第二，虽然关键字是多余的，但这可能不是它可选的原因.我相信这是因为 SQL Server 允许您在 DELETE 语句中指定一个 JOIN，并且强制第一个 FROM 使其变得尴尬.

Second, although the keyword is redundant, that's probably not why it's optional. I believe that it's because SQL Server allows you to specify a JOIN in the DELETE statement, and making the first FROM mandatory makes it awkward.

例如，这是一个普通的删除:

For example, here's a normal delete:

DELETE FROM Employee WHERE ID = @value

这可以缩短为:

DELETE Employee WHERE ID = @value

而 SQL Server 允许您使用 JOIN 基于另一个表进行删除:

And SQL Server allows you to delete based on another table with a JOIN:

DELETE Employee
FROM Employee
    JOIN Site
       ON Employee.SiteID = Site.ID
WHERE Site.Status = 'Closed'

如果第一个 FROM 关键字不是可选的，则上面的第二个查询需要如下所示:

If the first FROM keyword were not optional, the second query above would need to look like this:

DELETE FROM Employee
FROM Employee
    JOIN Site
       ON Employee.SiteID = Site.ID
WHERE Site.Status = 'Closed'

上述查询完全有效并且确实可以执行，但这是一个非常难读的查询.很难说这是一个单一的查询.由于重复"FROM 子句，看起来两个被混在一起了.

This above query is perfectly valid and does execute, but it's a very awkward query to read. It's hard to tell that it's a single query. It looks like two got mashed together because of the "duplicate" FROM clauses.

旁注:您的示例子查询可能是不确定的，因为没有 ORDER BY 子句.

Side note: Your example subqueries are potentially non-deterministic since there is no ORDER BY clause.

这篇关于SQL 中 DELETE 和 DELETE FROM 的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！