What is the difference between TRUNCATE, DELETE and DROP statements?

In this blog, we will learn the difference between TRUNCATE, DELETE, and DROP statements that are mostly used interchangeably but are totally different. So, let's get started.

TRUNCATE

TRUNCATE Command is a Data Definition Language operation. It is used to remove all the records from a table. It deletes all the records from an existing table but not the table itself. The structure or schema of the table is preserved.

Truncate command marks the table for deallocation. This operation removes all the data from a table bypassing a number of constraints enforced on the table. MySQL does not allow the users to truncate the table which is referenced as FOREIGN KEY in another table.

TRUNCATE TABLE statement is a DDL command so it can not be rolled back.

The Truncate command resets the AUTO_INCREMENT counters on the table.

MySQL truncates the table by dropping and creating the table. Thus, the DELETE triggers for the table do not fire during the truncation.

Syntax

The syntax for the TRUNCATE TABLE statement in MySQL is:

TRUNCATE TABLE [database_name.]table_name;
[database_name.] is optional. It is used to specify the name of the database in which the table exists.


[table_name] specifies the name of the table we want to truncate.

For example:

TRUNCATE TABLE Employees;
This query will remove all the records from the table Employees.


TRUNCATE TABLE Company.Employees;
This query will remove all the records from the table Employees in the database Company.

Truncate statement is equivalent to DELETE operation without a WHERE clause. The truncate command removes the records from a table without scanning it. This is why it is faster than the DELETE statement.

DELETE

The DELETE statement in SQL is a Data Manipulation Language(DML) Command. It is used to delete existing records from an existing table. We can delete a single record or multiple records depending on the condition specified in the query.

The conditions are specified in the WHERE clause of the DELETE statement. If we omit the WHERE clause then all of the records will be deleted and the table will be empty.

The DELETE statement scans every row before deleting it. Thus it is slower as compared to TRUNCATE command. If we want to delete all the records of a table, it is preferable to use TRUNCATE in place of DELETE as the former is faster than the latter.

DELETE is a DML Command so it can be rolled back.

The DELETE command returns the number of records that were deleted by its execution.

Syntax

The syntax for the DELETE statement in SQL is:

DELETE FROM table_name [WHERE conditions];
table_name specifies the name of the table from which we want to delete the records.
[WHERE condirions] is optional. We can provide conditions to filter out the records to be deleted.

We do not need to list fields in the DELETE statement since we are deleting the entire row from the table.

For example:

DELETE FROM Employees WHERE Emp_Id = 7;
This query will delete the record(s) from Employees table where field Emp_Id has a value 7.


DELETE FROM Employees WHERE Emp_Id = 10 && Emp_Name = "John Doe";
This query will delete the record(s) of the Employee from Employees table whose Emp_Id is 7 and Emp_Name is John Doe.


DELETE FROM Employees;
This query will delete all the records from Employees table as WHERE clause and conditions are not specified.

DROP

DROP statement is a Data Definition Language(DDL) Command which is used to delete existing database objects. It can be used to delete databases, tables, views, triggers, etc.

A DROP statement in SQL removes a component from a relational database management system (RDBMS).

DROP is a DDL Command. Objects deleted using DROP are permanently lost and it cannot be rolled back.

Unlike TRUNCATE which only deletes the data of the tables, the DROP command deletes the data of the table as well as removes the entire schema/structure of the table from the database.

Syntax

DROP object object_name
object: Keyword representing the type of the database object.
object_name: It specifies the name of the object we want to delete.

For example:

DROP TABLE Employees;
This query will remove the whole table Employees from the database.


DROP DATABASE Company;
This query will delete the database Company.