What Is The Difference Between DELETE, DROP And TRUNCATE?

Delete command-

DELETE command is a DML (Data Manipulation Language) command that is used to delete existing records from the table.With the delete command users have to give a condition with where clause and that given condition decides which row/records will be deleted.

It can delete one or more rows/records of the table.The DELETE statement does not delete the table structure, It just deletes the records present inside it.

Syntax

DELETE FROM table_name WHERE condition;

Note: If the user omits the WHERE clause, all records in the table will be deleted.

Example

DELETE FROM Employee WHERE EmpID=15;

DROP command-

DROP command is a DDL( Data Definition Language) command that is used to drop the complete table from the database.DROP Command removes the table structure as  definition of the table, all the data, indexes, triggers, constraints, and permission specifications of that table.

Syntax

DROP  TABLE  table_name;

NOTE-With the Drop command, we don’t have to give any condition with a where clause. We only require the table name.

Example–

DROP Table Employee;

If we run the above DROP query then the entire table will be removed from the database. 

DROP DATABASE Company;

This query will delete the database Company.

TRUNCATE Command

TRUNCATE command is a DDL( Data Definition Language) command that is used to delete the whole data from a table but not the table itself .

With the Truncate command, we don’t have to give any condition with a where clause. We only require the table name.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE Employee;

Difference between DELETE and TRUNCATE

1. The DELETE command is used to delete a particular row/records(one or more) from the table, while the TRUNCATE command  will delete entire rows from a table.
2.  The DELETE command may have a WHERE clause in order to filter the records,while the TRUNCATE command does not have a WHERE clause. 
3. DELETE operation operates on data records and executes deletion one-by-one in the order of the queries processed.whereas TRUNCATE operates on data pages and executes deletion of the whole table data at a time.
4. DELETE removes  row-by-row one at a time from the table. However, TRUNCATE deletes entire table data at a time, that’s why TRUNCATE is faster than DELETE.

Difference between DROP and TRUNCATE

1. The DROP command is used to remove table structure and its contents.Whereas the TRUNCATE command is used to delete all the rows/records from the table,it will not remove the table structure.
2. The DROP command removes the space allocated for the table from memory.The TRUNCATE command does not free the space allocated for the table from memory.
3. In the DROP command the Integrity Constraints get removed f.Whereas in TRUNCATE command the Integrity Constraints will not get removed.
4. The DROP Command has slower performance than TRUNCATE because it deletes the table from the database after deleting the rows.The TRUNCATE command works faster than the DROP command because it deletes all the records from the table without any condition.