The DELETE statement is used when we want to delete some or all of the records in the table, while the TRUNCATE statement will delete entire rows from a table. DELETE is a DML command, since it only modifies the data in the table, whereas TRUNCATE is a DDL command. Both Delete and Truncate commands can be used to delete data from the table. Truncate can be used to remove all data from the table without maintaining the integrity of the table.
Alternatively, the delete statement can be used to delete specific data. With the delete command, we can't bypass integrity compliance mechanisms. The DELETE statement analyzes each row before deleting it, making it slower compared to the TRUNCATE command. If we want to delete all the records in a table, it is preferable to use TRUNCATE instead of DELETE, since the first one is faster than the second.
TRUNCATE should never be used if delete Trigger is defined in the table to perform some automatic cleaning or logging action when deleting rows. The Truncate command is used to reinitialize the table, it is a DDL command that removes all rows from the table. DELETE deletes records one by one and creates an entry for each and every deletion in the transaction log, while TRUNCATE demaps pages and creates an entry for de-mapping pages in the transaction log. TRUNCATE cannot be used in the table referenced by a FOREIGN KEY constraint, unless a table has a foreign key that references itself. However, I have seen it unintentionally truncated, breaking referential integrity and violating other restrictions. TRUNCATE always deletes all rows from a table, leaving the table empty and the table structure intact, whereas DELETE can conditionally delete if the where clause is used.
At a higher level, you might consider truncating a command similar to a Delete statement without a Where clause. When partitioning a table, individual partitions can be truncated in isolation, making it possible to partially delete all of the data in the table. If you have data that you don't want in a developing table, it's usually best to truncate it, since you don't risk filling up the transaction log. Truncate does not record any information, but it does record the demapping of the data page of the table in which TRUNCATE was activated. Therefore, DELETE operations can be undone (undo), whereas DROP and TRUNCATE operations cannot be undone.
Because TRUNCATE is a DDL (Data Definition Language) statement, it doesn't require a commit for the changes to be permanent. MySQL does not allow users to truncate the table referred to as a FOREIGN KEY in another table.
