Comparison of DELETE, TRUNCATE and DROP Commands in SQL – News Couple
ANALYTICS

Comparison of DELETE, TRUNCATE and DROP Commands in SQL


This article was published as a part of the Data Science Blogathon.

Inn

As part of performing operations on the Table, we often need to remove records or remove the entire table from the database, so we can use the DELETE, TRUNCATE, and DROP commands. People often get confused about which states between the DELETE, TRUNCATE, and DROP commands in MySQL and provide an example to help us understand the differences.

DELETE

MySQL’s DELETE command is one of the most powerful commands for eliminating unnecessary rows or data from a table, and it is a Data Manipulation Language command. Using the Delete query in MySQL, you can delete multiple rows from a table in a single query, which comes in handy when you need to delete many records from a table. This command does not affect the table’s attributes and structure, and the row data cannot be recovered once deleted. Making a backup of the database before deleting it is therefore strongly recommended so that you can restore it later if necessary.

Syntax of DELETE command:

To Delete all records:

DELETE FROM table_name;

To delete records based on conditions:

DELETE FROM table_name WHERE condition;

If the WHERE clause is missing from the query statement, all records in the table will be deleted. Otherwise, it specifies which record should be deleted based on the conditions provided.

TRUNCATE

MySQL’s TRUNCATE command removes complete data, ie all rows, from the table and does not alter the table’s structure. It is a Data Definition Language command. It is not possible to roll back the data after using this command, and you cannot retrieve the contents of the table with a flashback command. The TRUNCATE command drops and recreates the table, thus making it very efficient.

Syntax of TRUNCATE command:

TRUNCATE TABLE table_name;

In TRUNCATE, there is no WHERE clause used; the command deletes all the table’s records.

DROP

MySQL’s DROP command removes one or more tables from the database and their structures, attributes, and constraints. The data cannot be rolled back with this command since it permanently deletes the table and releases memory from the tablespace. It is impossible to drop a parent table with a foreign key constraint, and first, we have to remove the foreign key constraint or drop the child table.

Syntax of DROP command:

DROP TABLE table_name;

Example demonstrating the use of DELETE, TRUNCATE and DROP

In the following example, we will demonstrate the use of DELETE, TRUNCATE, and DROP commands:

Let’s create the Employee_detail table with EmployeeID as the primary key:

>> CREATE TABLE Employee_detail(

EmployeeID INT NOT NULL PRIMARY KEY,

FirstName varchar(20),

LastName varchar(20),

City varchar(20),

Salary INT);

Use the SHOW TABLES statement to check the tables present in the current database:

Commands in SQL

Insert the values ​​into the Employee_detail table, then use the SELECT command to view the contents:

>> INSERT INTO Employee_detail(EmployeeID, FirstName, LastName, City, Salary)

VALUES(1001, ‘Rahul’, ‘Sharma’, ‘Delhi’, 20000),

(1002, ‘John’, ‘Smith’, ‘Mumbai’, 10000),

(1003, ‘Eva’, ‘Jackson’, ‘Bangalore’, 15000),

(1004, ‘Lily’, ‘Mathew’, ‘Mumbai’, 10000),

(1005, ‘Jay’, ‘Gill’, ‘Delhi’, 5000),

(1006, ‘Tom’, ‘Ford’, ‘Bangalore’, 10000),

(1007, ‘Ananya’, ‘Sharma’, ‘Bangalore’, 30000),

(1008, ‘Rini’, ‘Verma’, ‘Mumbai’, 20000);

>> SELECT * FROM Employee_detail;

Commands in SQL

DELETE command with WHERE condition:

We want to delete the records where the salary of an employee exceeds 15000 here:

>> DELETE FROM Employee_detail WHERE Salary > 15000;

Commands in SQL

It shows that 3 rows are affected by this statement.

Using the below select statement, we can view the records of the table after deleting the records with salaries greater than 15000:

>> SELECT * FROM Employee_detail

Commands in SQL

DELETE command without WHERE condition:

If we use the DELETE command without the WHERE clause, it will delete all the records in the Employee_detail table, and we can cross-check this with the SELECT command, which shows that we have no records in our table.

>> DELETE FROM Employee_detail;

>> SELECT * FROM Employee_detail;

Commands in SQL

Using the SHOW TABLE statement, we can see that the DELETE query does not affect the Employee_detail table’s structure.

Commands in SQL

TRUNCATE command:

The TRUNCATE command removes complete records from the table, and we can cross-check this with the SELECT command, which shows that we have no records in our table.

>> TRUNCATE Employee_detail;

>> SELECT * FROM Employee_detail;

Truncate command

Using the SHOW TABLE statement, we can see that the TRUNCATE query does not affect the Employee_detail table’s structure.

Truncate Command

DROP command:

DROP command removes the Employee_detail table from the database, and we can cross-check this with the SELECT command, which shows that the Employee_detail table does not exist.

>> DROP TABLE Employee_detail;

>> SELECT * FROM Employee_detail;

Drop command

The DROP command eliminates the entire existence of a table, so the SHOW TABLE statement shows that there is no employee_detail table in the database.



Source link

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button