Difference Between Delete and Drop in SQL

In this post you will understand the main differences between Delete and Drop in SQL. Both Delete and Drop are the commands to remove the elements from the database.

DELETE:

Delete is a Data Manipulation Language.The DELETE command is used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

If WHERE clause is missing from the DELETE statement then by default all the tuples are removed from the relation, though the relation containing those tuples still exist in the schema. You can not delete an entire relation or domains or constraints using DELETE command.

The syntax of DELETE command is as follow:

DELETE FROM relation_name WHERE condition;

DROP:

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

he syntax of DROP command is as follow:

DROP SCHEMA schema_name RESTRICT;

DROP Table table_name ;


The Key Differences Between DELETE AND DROP:

1. DELETE command is used to remove some or all the tuples from the table. On the other hand, the DROP command is used to remove schema, table, domain or  Constraints from the database.
2. DELETE is a Data Manipulation Language command whereas, DROP is a Data Definition Language command.
3. DELETE can be used along with the WHERE clause but, DROP is not used along with any command.
4. Actions performed by the DELETE  command can be rollbacked, but not in the case of DROP command.
5. As DELETE  command do not delete the table hence, no space is freed whereas, DROP deletes the entire table frees the memory space.