This article explains the difference between delete and truncate and brief about delete and truncate.

Difference between DELETE and TRUNCATE

  Sr. No.    Delete    Truncate  
  1    DELETE command is used to delete the specific row or rows in the table.    The TRUNCATE command is used to delete all rows in the table.  
  2    This is a DML i.e. Data Manipulation Language command.    This is a DDL i.e. Data Definition Language command.  
  3    DELETE command allows WHERE clause to filter the rows and delete them.    While TRUNCATE command does not allow filters because it deletes all the rows in the table.  
  4    It is possible to rollback after DELETE command executed.    Rollback is not possible after TRUNCATE command executed.  
  5    DELETE command lock the table rows i.e. tuples before deleting the rows.    TRUNCATE command locks the entire table.  
  6    DELETE statement filter and delete the records in the table. So it is slower than TRUNCATE command.    TRUNCATE statement does not filter the records, it simply deletes all the records in the table. So it is faster than DELETE command.  

DELETE Command

DELETE command is a DML i.e. Data Manipulation Language command and it used to remove row or rows i.e. tuples in table. While we can delete the multiple records in table by filtering table using WHERE clause.

Syntax of Delete Command

DELETE FROM table-name WHERE condition;

Example of Delete Command

DELETE FROM employee WHERE designation = ‘clerk’;

After execution the above command, it deletes those the rows in employee table which designation is clerk.

TRUNCATE Command

TRUNCATE command is a DDL i.e. Data Definition Language command and it used for deleting all the rows or tuples in table. TRUNCATE command has not accepted WHERE clause and filter table. TRUNCATE command simply delete all the tuples in table.

Syntax of Truncate Command

TRUNCATE TABLE table-name;

Example of Truncate Command

After execution the above command, it delete all the rows in table employee.

LEAVE A REPLY

Please enter your comment!
Please enter your name here