3

SQL server:
Difference between :

Delete tblxyz where id=6

and : 

Delete from tblxyz  where id=6

Is their any difference between above queries ?

sagi
  • 40,026
  • 6
  • 59
  • 84
Manish Vij
  • 29
  • 1
  • 5

5 Answers5

5

enter image description hereThere is no difference if you see the execution plan both generates delete scripts as below

DELETE [testtable]  WHERE [numbers]=@1
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • 2
    Although the two are equivalent, testing an execution plan on a single query only shows that those two queries on a particular system produce the same plan. It says nothing general about the syntax itself. – Gordon Linoff Sep 13 '16 at 13:16
  • 2
    Note that ANSI SQL requires the `FROM` keyword. I.e. it's good programming practice keeping it, as it makes the code more portable. (And easier to read, at least for less MS SQL Server experienced users.) – jarlh Sep 13 '16 at 13:29
4

There is no direct difference between the two statements (except that I find the "DELETE FROM" easier to read and understand)

note that ANSI does require the "FROM" keyword as stated by jarlh

see also https://msdn.microsoft.com/en-us/library/ms189835.aspx

Razieltje
  • 357
  • 1
  • 7
0

According to the MSDN the word "from" is optional. The default is not to use:

FROM

and the target table_or_view_name, or rowset_function_limited.An optional keyword that can be used between the DELETE keyword

However in MS Access (or may be some other databases) you may delete a row using delete * command, so you have to use from:

delete * from phoneBook where....
Ali Sheikhpour
  • 10,475
  • 5
  • 41
  • 82
  • 1
    There is no such thing as deleting a column not a row. You can update a column to null or drop a column but not delete it. – Martin Smith Sep 13 '16 at 13:12
0

There is no difference in your delete.

However, the FROM clause is a fully functional from clause, so you can use it with JOIN. For instance:

delete t
    from t join
         (select min(id) as minid
          from t
          group by grp
         ) tt
         on t.id = tt.minid;

This would delete the record with the minimum id for each grp value. The alias after the delete is needed to specify which table to delete from (although in this case, deleting from an aggregation result is not allowed).

Note: This query is for illustrative purposes. There is nothing wrong with the query, but it is not how I would actually write such a query in SQL Server.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That is a different `FROM`. The grammar supports two. The one they are asking about is the first one. That would be `DELETE FROM t` in your example code. The second one is ` FROM table_source ` the `FROM` is not optional in the code you posted. – Martin Smith Sep 13 '16 at 13:17
0

The from Clause is optional..Below is stripped down version of Syntax..

DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ]
{ { table_alias

FROM An optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94