Difference between Delete, Truncate and Drop Statements in MySQL

Introduction

 

In this tutorial, I am going to explain the delete, truncate, and drop statements of MySQL with various examples. Without wasting time, let’s start.

 

In a MySQL table, if I have to modify and delete then we have to use some statements on the table. But these commands are different from each other also in case of working. Thus, I have to explain the differences between DELETE, DROP, and TRUNCATE statements of MySQL.

 

DELETE Statement

 

If you want to delete a record(s) from a table then, you can use the MySQL DELETE statement.

 

Syntax:

DELETE FROM <table_name>

WHERE <condition> ;

 

Note:

If you don’t specify the WHERE condition in the DELETE statement then, it will delete all rows in the table.

 

For Example 

 

Use the following table 'entrepreneur' for the demonstration purpose and then apply the DELETE statement.

 

First, create a new table named 'entrepreneur':

CREATE TABLE Entrepreneur(
     E_id int AUTO_INCREMENT PRIMARY KEY,
     Company_Name varchar(100) NOT NULL,
     Specialization varchar(100) NOT NULL
);

Now, insert some dummy data into it:

INSERT INTO Entrepreneur VALUES
     (1, ‘Vatsa’, ‘IT’),
     (2, ‘OEnterprise, ‘Marketing’),
     (3, ‘RBanqut’, ‘Hotel’),
     (4, ‘JMahal’, ‘Hotel’),
     (5, ‘SMall’, ‘Marketing’),
     (6, ‘LShop’, ‘Marketing’),
     (7, ‘DCompany’, ‘IT’);

Check the data of the Entrepreneur Table:

SELECT * FROM Entrepreneur;

Finally, use the DELETE statement to delete the rows.

 

1)     If you want to delete the companies name whose “Specialization” is “Hotel”. Then use:

DELETE FROM Entrepreneur
WHERE Specialization = ‘Hotel’;

2)     To delete all the rows from the Entrepreneur table, use:

DELETE FROM Entrepreneur;
DELETE statement features

The DELETE statement deletes table rows and returns the number of rows deleted. We can delete a selected no of records from the table using DELETE statement. DELETE statement has two modifiers namely LOW_PRIORITY and QUICK.

• When we have to use DELETE Command then the data deleted can be retrieved when you ROLLBACK.
• The QUICK modifier is specified then the table handler does not merge index leaves during delete, this may cause to speed up certain kind of deletes.
• LIMIT clause can also be used to set a limit on the number of rows to be deleted.
• A DELETE is issued with no WHERE clause, all rows are deleted.
• ORDER BY clause can be used in the DELETE statement. In this case, the rows are deleted in the specified order.

Note:   With the LOW_PRIORITY keyword, DELETE execution is delayed until no other clients are reading from the table and If WHERE clause is specified then rows are deleted satisfying the given conditions and finally returns the number of rows deleted. With no WHERE clause in AUTOCOMMIT mode, DELETE works as TRUNCATE and does not return affected rows.

 

For Example:

DELETE FROM Entrepreneur
WHERE E_id < 4;

TRUNCATE Statement

 

In MySQL, the TRUNCATE statement is used to delete all the rows/data in a table. You can say, TRUNCATE statement is like a DELETE statement without a WHERE condition.

 

Syntax

TRUNCATE TABLE <table_name>;

 

First, create a new table named 'entrepreneur':

CREATE TABLE Company(
     E_id int AUTO_INCREMENT PRIMARY KEY,
     Company_Name varchar(100) NOT NULL,
     Field varchar(100) NOT NULL
);

Now, insert some dummy data into it:

INSERT INTO Company VALUES
     (1, ‘Vatsa’, ‘IT’),
     (2, ‘OEnterprise, ‘Marketing’),
     (3, ‘RBanqut’, ‘Hotel’),
     (4, ‘JMahal’, ‘Hotel’),
     (5, ‘SMall’, ‘Marketing’),
     (6, ‘LShop’, ‘Marketing’),
     (7, ‘DCompany’, ‘IT’);

Check the data of the Company Table:

SELECT * FROM Company;

Now, use the TRUNCATE statement to delete all the rows from a table named “Company”.

TRUNCATE TABLE Company;

TRUNCATE statement features

 

TRUNCATE TABLE differs from DELETE in the following ways :

• TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
• Once you use TRUNCATE command then u cannot retrieve the data again from the table.
• TRUNCATE is not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
• TRUNCATE removes all the rows from the Table.
• TRUNCATE does not return the number of deleted rows.
• TRUNCATE functionality is an Oracle SQL extension adopted in MySQL.
• TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.

When TRUNCATE is not transaction-safe, an error occurs when attempting one in the course of an active transaction or active table lock. With TRUNCATE TABLE, a table can be re-created (as an empty table) when only the table format file tbl_name.frm is valid, and its data or index files have become corrupted. With TRUNCATE TABLE, the table handler reset the AUTO_INCREMENT value starting from the beginning

 

Note:

In the case of partitioned tables, TRUNCATE TABLE drops the data and index files and re-creates but preserves the partition definitions (.par) file. TRUNCATE TABLE works similarly to DELETE if there are foreign key constraints that reference the table; otherwise, it simply drops and re-creates the table. The AUTO_INCREMENT value is reset by TRUNCATE TABLE even ignoring foreign key constraints if there is.

 

Note:

After truncate this table do not contains any fields but it show in our database by using the following command.

SHOW TABLES;

DROP Statement

 

DROP statement is used to delete the table structure with all the data. DROP statement is used to delete a table and its data permanently.

 

Syntax

DROP TABLE <table_name>;

 

Note:

Users can drop multiple tables by using a comma-separated list.

 

Syntax

DROP TABLE (table_name1, table_name2);

 

First, create a new table named 'entrepreneur':

CREATE TABLE Entrepreneur(
     E_id int AUTO_INCREMENT PRIMARY KEY,
     Company_Name varchar(100) NOT NULL,
     Field varchar(100) NOT NULL,
     Address varchar(100),
     Asset DEC(10, 2) NOT NULL,
     Cost DEC(10, 2) NOT NULL
);

Ow, drop a table by using the following command.

DROP TABLE Entrepreneur

DROP statement features

 

The DROP statement deletes the data as well as structure.

 

Difference

 

The difference between DROP and DELETE table is that, after executing DELETE statement the contents of the table are removed but the structure remains the same, but in the case of the DROP statement both the contents and structure are removed.

 

CONCLUSION

 

In this article, I have discussed the concept of delete, truncate, and drop statements of MySQL with various examples.

 

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 

 

Thanks for reading this article!