Difference between DELETE and DROP in SQL
Prerequisite – SQL Commands
DELETE is a Data Manipulation Language (DML) command and 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 which satisfy the WHERE clause condition but if WHERE clause is missing from the DELETE statement then by default all the tuples present in relation are removed.
The syntax of DELETE command:
DELETE FROM relation_name WHERE condition;
DROP is a Data Definition Language (DDL) command which removes the named elements of the schema like relations, domains or constraints and you can also remove an entire schema using DROP command.
The syntax of DROP command:
DROP SCHEMA schema_name RESTRICT; DROP Table table_name CASCADE;
Comparison Chart:
| Parameter | DELETE | DROP |
|---|---|---|
| Basic | It removes some or all the tuples from a table. | It removes entire schema, table, domain, or constraints from the database. |
| Language | Data Manipulation Language command | Data Definition Language command. |
| Clause | WHERE clause mainly used along with the DELETE command. | No clause required along with DROP command. |
| Rollback | Actions performed by DELETE can be rolled back as it uses buffer. | Actions performed by DROP can’t be rolled back because it directly works on actual data. |
| Space | space occupied by the table in the memory is not freed even if you delete all the tuples of the table using DELETE | It frees the table space from memory |
| Main Issue | Shortage of memory | Memory fragmentation |
| Locality of reference | Excellent | Adequate |
| Flexibility | Fixed size | Resizing is possible |
Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.
Recommended Posts:
- Difference between DELETE, DROP and TRUNCATE
- Difference between DROP and TRUNCATE in SQL
- Difference between DELETE and TRUNCATE
- SQL | DROP, TRUNCATE
- MySQL | DROP USER
- Neo4j Drop Index
- SQL | ALTER (ADD, DROP, MODIFY)
- Create, Alter and Drop schema in MS SQL Server
- How to drop one or multiple columns in Pandas Dataframe
- How to delete last element from a set in C++
- SQL | DELETE Statement
- How to delete last element from a map in C++
- PHP | MySQL Delete Query
- Delete all the nodes from the list which are less than K
- Delete Operation in B-Tree
- Neo4j Delete Node
- How to delete data from file in Python
- Delete all the even nodes from a Doubly Linked List
- Delete all the even nodes of a Circular Linked List
- Delete all the nodes from the list which are divisible by any given number K
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : NikhilKoyikkamannil
