Difference between DELETE and DELETE FROM in SQL?

Question

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) 

Would that do the same thing as:

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?

Answer 1

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.

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

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 

And that can be shortened to:

DELETE Employee WHERE ID = @value 

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' 

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' 

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.

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