Difference between DELETE and DELETE FROM in SQL?
Hi friends there is no difference between delete and delete from in oracle database it is optional, but this is standard to write code like this
DELETE FROM table [ WHERE condition ]
this is sql-92 standard. always develop your code in the standard way.
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 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.