Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference Between DELETE and DROP in SQL
DELETE is a Data Manipulation Language (DML) command. It is used to remove tuples/records from a relation/table. On the other hand, DROP is a Data Definition Language (DDL) command and is used to remove named elements of schema like relations/table, constraints or entire schema.
Read this article to learn more about DELETE and DROP commands in SQL and how they are different from each other.
What is DELETE in SQL?
In SQL, the DELETE command is used to remove some or all the records from a database table. With the DELETE command, the WHERE clause is used to define the filter criteria so that it can remove only the selected records from a table.
When the DELETE command is executed, a log file is created in the transaction log. In case we deleted a record mistakenly, we can retrieve that by using the ROLLBACK command. But, the ROLLBACK command can only be used before the COMMIT command.
Syntax
DELETE from table_name where condition;
What is DROP in SQL?
In SQL, the DROP command is used to remove named elements of a schema like table, constraint, or the whole schema itself. Since the DROP command completely removes the data, hence it frees the memory space. Also, once the DROP command is executed, it cannot be rolled back.
Syntax
DROP table_name;
Now, let us discuss the differences between DELETE and DROP in detail.
Difference between DELETE and DROP in SQL
The following are the important differences between DELETE and DROP in SQL −
Key |
DELETE |
DROP |
|---|---|---|
Purpose |
DELETE Command removes some or all tuples/records from a relation/table |
DROP Command removes named elements of schema like relations/table, constraints or entire schema. |
Language |
DELETE is a DML command. |
DROP is a DDL command. |
Clause |
WHERE clause is used to add filtering. |
No WHERE clause is available. |
Rollback |
Delete command can be roll backed as it works on data buffer. |
Drop command cannot be roll-backed as it works directly on data. |
Memory Space |
Table memory space is not free if all records are deleted using DELETE Command. |
Drop command frees the memory space. |
Problem |
DELETE command may face shortage of memory. |
DROP Command may cause memory fragmentation. |
Interaction |
SQL directly interacts with database server. |
PL/SQL does not directly interacts with database server. |
Orientation |
SQL is data oriented language. |
PL/SQL is application oriented language. |
Objective |
SQL is used to write queries, create and execute DDL and DML statements |
PL/SQL is used to write program blocks, functions, procedures, triggers and packages. |
Conclusion
The most significant difference that you should note here is that DELETE is a Data Manipulation Language (DML) command, whereas DROP is a Data Definition Language (DDL) command.
14K+ Views
