DROP command in SQL removes the table form the database, DELETE command removes one or more records from the table, and TRUNCATE command removes all the rows from the existing table. In this article, we will discuss the difference between DROP, DELETE and TRUNCATE command in SQL.
DROP, DELETE, and TRUNCATE commands in SQL are used to remove the record from the table, and in the previous articles, we have seen the working of all these three commands. We have also briefly discussed the difference between DROP and DELETE statements and difference between DELETE and TRUNCATE statements briefly with the help of examples.
But, it is quite evident that people confuse these three commands.
So in this article, we will discuss how these three commands are different from each other with the help of an example, i.e., the difference between DROP DELETE and TRUNCATE commands in SQL.
Must Read: Introduction to SQL
Must Check: Database and SQL Online Courses & Certifications
Table of Content
- Difference Between DROP, DELETE, and TRUNCATE: DROP vs DELETE vs TRUNCATE
- DROP Command in SQL
- DELETE Command in SQL
- TRUNCATE Command in SQL
- Example of DROP, DELETE, and TRUNCATE Command in SQL
Difference Between DROP DELETE and TRUNCATE: DROP vs DELETE vs TRUNCATE
| DROP | DELETE | TRUNCATE | |
| Definition | It completely removes the table from the database. | It removes one or more records from the table. | It removes all the rows from the existing table |
| Type of Command | It is a DDL command | It is a DML command | It is a DDL command |
| Syntax | DROP TABLE table_name; | DELETE FROM tble_nameWHERE conditions; | TRUNCATE TABLE table_name; |
| Memory Management | It completely removes the allocated space for the table from memory. | It doesn’t free the allocated space of the table. | It doesn’t free the allocated space of the table. |
| Effect on Table | Removes the entire table structure. | Doesn’t affect the table structure | Doesn’t affect the table structure |
| Speed and Performance | It is faster than DELETE but slower than TRUNCATE as it firstly deletes the rows and then the table from the database. | It is slower than the DROP and TRUNCATE commands as it deletes one row at a time base on the specified conditions. | It is faster than both the DELETE and DROP commands as it deletes all the records at a time without any condition. |
| Use with WHERE clause | Not applicable as it operates on the entire table | Can be used | Can’t be used as it is applicable on the entire table |
Note: Use DROP and TRUNCATE commands with caution, as they can lead to loss of data.
What is DROP Command in SQL?
The DROP command in SQL removes all the table definitions and all the data from one or more database tables. In simple terms, it deletes an entire table or database.
- The DROP command removes not only the table but also the table’s structure.
- DROP Command is irreversible, i.e., once you drop the table or the database, you can’t reverse it.
- Commonly used when the table or database is no longer in use and you want to free up storage space.
- Using the DROP command, you can drop multiple tables/database at a time.
Syntax of DROP Command in SQL
DROP a Table
DROP TABLE table_name;
DROP a Database
DROP DATABASE database_name;
What is DELETE Command in SQL?
The DELETE command in SQL removes one or more rows from the table based on the condition specified by the user. i.e., it allows the user to specify the condition to remove the rows from the table.
- They are typically used when you want to remove any particular record from the table based on certain conditions.
- DELETE command is always used with the SQL WHERE Clause.
- If the SQL WHERE clause is not used with the DROP command, it will remove all the rows from the table.
- DELETE command is slower than both the DROP and TRUNCATE commands as it removes the records one by one.
Syntax for SQL DELETE Command
DELETE FROM table_name WHERE conditions;
What is TRUNCATE Command in SQL?
TRUNCATE Command in the SQL removes all the records from the table efficiently and quickly and efficiently as it doesn’t log individual row deletion, which makes it faster while working with the larger table.
- Typically used when you want to remove all the records from the table but also want to keep the table structure intact.
- Similar to the DROP, it is also irreversible.
Syntax for SQL DELETE Command
TRUNCATE TABLE table_name;
Till now, you have a clear understanding of what is DROP, DELETE, and TRUNCATE command in SQL and how they differ from each other based on different parameters.
Now, let’s take an example to get a better understanding of how to use these three commands to remove the records.
Example of DROP, DELETE, and TRUNCATE Command in SQL
Let’s take a dataset named ‘Orders’ having the column name ‘OrderID‘, ‘CustomerID‘, ‘OrderDate‘, and ‘Product‘.
| OrderID | CustomerID | OrderDate | Product |
| 1 | 1001 | 2023-01-01 | Apples |
| 2 | 1002 | 2023-01-02 | Bananas |
| 3 | 1003 | 2023-01-03 | Grapes |
| 4 | 1004 | 2023-01-04 | Oranges |
How to use DELETE Command in SQL?
DELETE FROM Orders WHERE OrderID = 1;
Output
| OrderID | CustomerID | OrderDate | Product |
|---|---|---|---|
| 2 | 1002 | 2023-01-02 | Bananas |
| 3 | 1003 | 2023-01-03 | Grapes |
| 4 | 1004 | 2023-01-04 | Oranges |
How to use TRUNCATE Command in SQL?
TRUNCATE TABLE Orders;
Output
The ‘Orders’ table would be empty, but the structure of the table would still exist.
How to use DROP Command in SQL?
DROP TABLE Orders;
Output
The ‘Orders’ table would no longer exist in the database.
Conclusion
DROP, DELETE, and TRUNCATE commands are used to remove the records from the dataset. But all three serves different purposes. In this article, we have discussed the difference between all three commands on the basis of different parameters. At the end we have taken a dataset and perform all three commands on that to get a better understanding of how to use these commands.
Hope you will like the article.
Keep Learning!!
Keep Sharing!!
Related Reads
FAQs
What is the main difference between DROP, DELETE, and TRUNCATE in SQL?
DROP and TRUNCATE are DDL command while DELETE is a DML command. DELETE remove the specific row based on the given condition, TRUNCATE removes all the record from the table at once, whereas the DROP command removes the table or databases and as well as the structure.
Does DELETE command remove the table structure?
No DELETE command doesn't remove the table structure, it deletes the specific row of the table while keeping the table structure intact.
What happens when you use a DROP command on a table?
It remove the table or database as well as the structure of the table.
Can you rollback data after using DELETE, TRUNCATE, and DROP?
Yes, you can rollback the data after using DELETE command since it is a DML statement. But if you have used the DROP and TRUNCATE command you can't rollback.
Which is faster: DELETE or TRUNCATE?
TRUNCATE is faster than DELETE command as it removes all the record from the table at once. Whereas DELETE command removes the record one-by-one.
Does TRUNCATE free up space?
No TRUNCATE command do not free up space. It only remove all the record from the database keeping the structure intact.
What is the difference between DELETE and DROP?
DELETE is a DML statement, whereas the DROP is a DDL statement. If you apply DELETE it will remove the specified record from the table, but DROP command removes the table as well as its structure.
Can TRUNCATE have a WHERE clause?
No, TRUNCATE can not have a WHERE clause as it removes all the record from the table at once.
DO TRUNCATE and DROP need a commit?
No delete and truncate statement do not need a commit to make changes permanent.
Download this article as PDF to read offline
Download as PDF
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Trending Technology Courses
Top Picks & New Arrivals
Comments