Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is DELETE Command in SQL?
2.1.
Syntax
2.2.
Example
3.
What is DROP Command in SQL?
3.1.
Syntax
3.2.
Example
4.
What is TRUNCATE Command in SQL?
4.1.
Syntax
4.2.
Example 
5.
Difference Between DELETE, DROP and TRUNCATE Commands in SQL
6.
Frequently Asked Questions
6.1.
Is TRUNCATE permanent or temporary?
6.2.
Which is faster DROP or TRUNCATE?
6.3.
What is the difference between DELETE and DROP and TRUNCATE?
6.4.
What is the difference between DROP table and DELETE table?
6.5.
Is TRUNCATE DDL or DML?
6.6.
Which is faster DELETE or TRUNCATE?
7.
Conclusion
Last Updated: Apr 23, 2024
Easy

Difference between DELETE, DROP and TRUNCATE

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In SQL, the DROP command removes a table from the database, DELETE removes records from a table, and TRUNCATE removes all rows from a table.

Difference between DELETE, DROP and TRUNCATE

We will discuss about DELETE, DROP, and TRUNCATE statements briefly. In this article, we will also discuss DROP vs TRUNCATE vs DELETE statements by discussing their concept, syntax, and properties. We will also look into some examples to understand the same by outlining some of the critical features and by presenting a tabular format to understand the comparison.

What is DELETE Command in SQL?

The delete statement is an SQL command used to remove one or more rows from a table based on a specific condition. It is a DML (data manipulation command). This command allows us to remove unwanted or obsolete data from a database and can be used to delete single rows.

Unwanted or obsolete data refers to data that is no longer needed or useful but they do take up space in the Database for example incorrect, duplicated, or outdated data.

Syntax

DELETE FROM table_name WHERE condition;

 

In the above syntax, ‘table_name’ is the name of the table from which we want to delete rows, and ‘condition’ is the criteria on the basis of which a row is deleted.

Example

DELETE FROM NinjaClass WHERE number<3;


The above SQL query will delete all the rows from the “NinjaClass” table where the number is less than 3.

The “NinjaClass” table has columns named “id”,”NinjaName” and “NinjaSkills”.Below is an example of what the output would look like

Before executing the DELETE statement, Make a table.

Code

-- create a table

CREATE TABLE NinjaClass (
  id INT PRIMARY KEY,
  name varchar(30) NOT NULL,
  position varchar(30) NOT NULL
);

-- insert some values
INSERT INTO NinjaClass VALUES (1, 'Ninja1', 'Developer');
INSERT INTO NinjaClass VALUES (2, 'Ninja2', 'Analyst');
INSERT INTO NinjaClass VALUES (3, 'Ninja3', 'Analyst');
INSERT INTO NinjaClass VALUES (4, 'Ninja4', 'Developer');

-- fetch some values
SELECT * FROM NinjaClass;


Output

Output

After executing the DELETE statement

Code

DELETE FROM NinjaClass WHERE id<3;
-- fetch some values
SELECT * FROM NinjaClass;


output

Output

The delete statement permanently removes the data from the table. Once deleted, it cannot be undone. Therefore DELETE statement should be used with caution. We can also take a table backup before performing any delete operation on it. 

Learn more, wget command in linux

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is DROP Command in SQL?

The DROP statement in SQL allows us to remove an entire table, along with all its data, indexes and triggers from the database. Once executed, the table and its data are permanently deleted.

A trigger is a special type of program that is stored in the database’s memory and has a unique name. This program automatically runs when something happens in a database, like when an existing row is changed or when a new row is added.

Syntax

DROP TABLE table_name;

 

The name of the table you wish to drop is indicated here by table_name. The table, together with all of the related data, triggers, indexes, and other objects, will be permanently removed from the database upon the execution of this statement. It's important to remember that dumping a table is a strong move that cannot be reversed. Thus, before using the DROP TABLE statement, make sure you have a backup of any crucial data.

Example

DROP TABLE NinjaClass;

 

Once executed, the statement, the entire “NinjaClass” table, and all of its data, triggers, and indexes will be permanently deleted from the database.

Indexes are used for quick data retrieval

Code

DROP TABLE NinjaClass;


Output

Output

What is TRUNCATE Command in SQL?

This statement allows us to remove all the data from the table by maintaining the table structure. The Truncate statement can be rolled back using the ROLLBACK statement. It is much faster than the DELETE statement. But it cannot remove specific rows based on a condition.

Syntax

TRUNCATE TABLE table_name;

Example 

TRUNCATE TABLE NinjaClass;

 

In the above example, all the data in the “NinjaClass” table will be deleted, and the table structure will be left intact.

When executed,

Truncate TABLE NinjaClass;
SELECT * FROM NinjaClass;

 

Output

Output

Difference Between DELETE, DROP and TRUNCATE Commands in SQL

BasisDELETEDROPTRUNCATE
OperationThe DELETE statement removes specific rows based on specific conditionsThe DROP statement Removes an entire table, including all its indexed data and triggers.It removes all the data from the table. This statement leaves the table structure intact
SpeedThe DELETE statement is slower than the TRUNCATE and DROP statements.The DROP statement is slower than the Truncate and faster than the DELETE statementFaster than DROP and DELETE
Log impactDELETE statement Logs individual row deletions.The DROP statement Logs each action as a separate transaction.It does not log individual row deletions
UsageThe DELETE command removes one or more records that already exist in the database table.The DROP statement helps delete an entire table, along with all its data.Useful for rapidly deleting all data from a table while keeping the table structure unchanged.
ReversibilityIt is partially reversible. It can be undone using the ROLLBACK command within a transaction. But when a transaction is committed, the deleted data cannot be recovered.It is non-reversible. We cannot recover the data unless it’s been backed up.It is partially reversible and can be undone using the ROLLBACK command within a transaction. But when a transaction is committed, the deleted data cannot be recovered.
AutonumberingIdentity column values are not resetIdentity column values are not resetIdentity column values can be reset with extra syntax

Also see, IEnumerable vs IQueryable

Frequently Asked Questions

Is TRUNCATE permanent or temporary?

TRUNCATE is permanent. It preserves structure while deleting all table data. faster than DELETE, but irreversible too. Not logging rows individually.

Which is faster DROP or TRUNCATE?

TRUNCATE swiftly eliminates all data while preserving the table structure, making it generally faster than DROP. The table and its related objects are deleted when you use DROP.

What is the difference between DELETE and DROP and TRUNCATE?

DELETE removes specific rows from a table while keeping the table structure intact. DROP deletes the entire table along with its structure. TRUNCATE removes all rows from a table, but preserves its structure.

What is the difference between DROP table and DELETE table?

DROP table removes the entire table along with its structure. DELETE table deletes specific rows from a table while keeping the table structure intact.

Is TRUNCATE DDL or DML?

TRUNCATE is a DDL (Data Definition Language) statement.

Which is faster DELETE or TRUNCATE?

TRUNCATE is generally faster than DELETE due to its minimal logging and direct deallocating of data pages.

Conclusion

We hope this article helped you understand the difference between delete drop and truncate statement. This article discussed some of the differences between delete drop and truncate statements by briefly discussing their concept, syntax, and properties. We also looked into some examples to understand the same. We have outlined some of the key features and presented a tabular format to understand the comparison. You can read more such articles on our platform, Coding Ninjas Studio.

Also, Read-

You will find straightforward explanations of almost every topic on this platform. So take your coding journey to the next level using Coding Ninjas.

Happy coding!

Previous article
Difference Between DELETE and TRUNCATE
Next article
Difference between Oracle and SQL Server
Live masterclass