Difference Between Delete and Drop

删除vs Drop

Both Delete and Drop commands belong to SQL (Structured Query Language) statements, and they are used in case of removing data from a database. Delete is a DML (Data Manipulation Language) command. It deletes some or all the data from a table according to the condition that the user has specified. Delete statement removes only the data records in the table, but the table structure presents same in the database. Drop command is a DDL (Data Definition Language) statement, and it acts in a different way from the Delete command. It is not a conditional based statement, so deletes entire data from the table, also it removes the table structure and all the references to that table permanently from the database.

删除Statement

如前所述,Delete语句删除数据from a table based on the condition provided, and the Where clause is used with Delete to specify this required condition. If the Where clause is not stated with Delete, all the table data is removed from the table. However, in the Delete operation, the existing table structure remains same. Therefore, user does not need to define the table structure if he/she wants to reuse the table again. As Delete is a DML command, it does not commit automatically after execution. So, this can be rolled back in order to undo the previous operation. Otherwise, the Commit statement should be called to make the changes permanent. While executing the Delete statement, it records an entry in the transaction log for each row deletion. So, this affects to slow down the operation. As well, it does not deallocate the space used after execution.

Following is the syntax for Delete statement.

DELETE FROM

or

DELETE FROM WHERE

Drop Statement

Drop statement removes not only all the table records from the database without any condition, but also it removes the table structure, integrity constraints, indexes, and access privileges of the relevant table from the database permanently. So, all the relationships for other tables also no longer exist, and information about the table is removed from the data dictionary. So, if the user wants to reuse the table he/she needs to define the table structure and all other references to the table again. Drop is a DDL command and after the execution of the command, it cannot be rolled back again, because Drop command uses an auto commitment. Therefore, user should be very careful in using this command. Drop statement cannot be applied on system tables, and also it cannot be used for the tables which have foreign key constraints.

Drop command can be used not only for SQL tables, but also for databases, views and table columns, and all the data stored in these objects are lost forever along with the objects.

Following is the typical syntax for Drop command.

DROP TABLE

What is the difference between Delete and Drop? 1. Delete and Drop commands removes table data from a database. 2. But Delete statement performs conditional based deletion, whereas Drop command deletes entire records in the table. 3. Also, Delete statement removes only the rows in the table and it preserves the table structure as same, whereas, Drop command removes all the data in the table and the table structure, also it removes all other references from the database. 4. Delete is a DML statement, whereas Drop is a DDL command. So, Delete operation can be rolled back and it is not auto committed, while Drop operation cannot be rolled back in any way as it is an auto committed statement. 5. Drop command cannot be used on the tables that have been referenced by foreign key constraints, whereas Delete command can be used instead of that. 6. Drop command should be used carefully with a good understanding compared to the Delete statement in SQL applications.