Difference Between DELETE and DROP in SQL (with Comparison Chart)

Table of Contents

delete-vs-drop DELETE and DROP are the commands used to remove the elements of the database. DELETE command is a Data Manipulation Language command whereas, DROP is a Data Definition Language Command. The point that distinguishes DELETE and DROP command is that DELETE is used to remove tuples from a table and DROP is used to remove entire schema, table, domain or constraints from the database.

Let us discuss some more differences between DELETE and DROP command in SQL with the help of comparison chart below.

Content: DELETE Vs DROP

Comparison Chart

Basis for Comparison	DELETE	DROP
Basic	DELETE remove some or all the tuples from a table.	DROP can remove entire schema, table, domain, or constraints from the database.
Language	DELETE is a Data Manipulation Language command.	DROP is a Data Definition Language command.
Clause	WHERE clause can be used along with the DELETE command.	No clause is used along with DROP command.
Rollback	Actions performed by DELETE can be roll-backed.	Actions performed by DROP can not be rollbacked.
Space	Even if you delete all the tuples of the table using DELETE, space occupied by the table in the memory is not freed.	Table deleted using DROP frees the table space from memory.

Definition of DELETE

DELETE is a Data Manipulation Language (DDL) command. DELETE command is used when you want to remove some or all the tuples from a relation. If WHERE clause is used along with the DELETE command, it removes only those tuples that satisfy the WHERE clause condition.

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;

If you link two tables using a foreign key and delete a tuple from a referenced table then automatically the tuple from referencing table will also be deleted in order to maintain the referential integrity.

To maintain referential integrity, DELETE has two behavioural options, RESTRICT and CASCADE. RESTRICT reject the deletion of tuple if it referenced by a referencing tuple in another table. CASCADE allows deletion of the referencing tuple that refers the tuple being deleted.

Definition of DROP

DROP is a Data Definition Language (DDL ) command. The DROP command removes the named elements of the schema like relations, domains or constraints, you can even remove an entire schema using DROP command.

The syntax of DROP command is as follow:

DROP SCHEMA schema_name RESTRICT;

DROP Table table_name CASCADE;

The DROP command has two behavioural options named CASCADE and RESTRICT. When CASCADE is used to DROP the schema, it deletes all the related elements like all relations in the schema, domains and constraints.

When you use CASCADE to remove a relation (table) from a schema, then it deletes all the constraints, views and also the elements that reference the relation that is being dropped.

In case you DROP a Schema using RESTRICT then, the DROP command executes only if the no elements in the schema are left. If you DROP a table using RESTRICT, then the DROP command will execute only if no elements in the table are left.

Key Differences Between DELETE and DROP in SQL

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.

DELETE is a Data Manipulation Language command whereas, DROP is a Data Definition Language command.

DELETE can be used along with the WHERE clause but, DROP is not used along with any command.

Actions performed by the DELETE command can be rollbacked, but not in the case of DROP command.

As DELETE command do not delete the table hence, no space is freed whereas, DROP deletes the entire table frees the memory space.

Conclusion

DELETE command is used to delete the rows inside a table and the DROP command is used to delete the complete table itself.

ncG1vNJzZmislZi1pbXFn5yrnZ6YsrR6wqikaJyZm7OmvsSnmp5lkprBuLHEp2SdnZyawaZ5wKebZpyipL1utc1mqqqkXp3Brrg%3D