DELETE vs TRUNCATE

DELETE and TRUNCATE both commands can be used to delete data of a table. 

Delete is a DML command whereas truncate is DDL command. Truncate can be used to delete the entire data of the table without maintaining the integrity of the table. On the other hand , delete statement can be used for deleting the specific data. With delete command we can’t bypass the integrity enforcing mechanisms.

Key	Delete	Truncate
Basic	It is used to delete specific data	It is used to delete the entire data of the table
Where clause	We can use with where clause	It can’t be used with where clause
Locking	It locks the table row before deleting the row	It locks the entire table
Rollback	We can rollback the changes.	We can’t rollback the changes
Performance	It is slower than truncate	It is faster than delete

DDL(Data Definition Language, like create, drop, alter, truncate, add, modify, change, rename...) is commited when executed anyway.

DML(Data Manipulation Language, like insert, delete, add, update, select...) is commited when executed by default. If autocommit is set to false, the following DML will be committed.

Let's look at an example.

# DELETE TABLE and ROLLBACK, autocommit is true by default
INSERT INTO emp SELECT * FROM employees;  # copy data
SELECT COUNT(*) FROM emp;   ### rollback here.
SET autocommit = FALSE;
DELETE FROM emp;
SELECT COUNT(*) FROM emp;
ROLLBACK;
SELECT COUNT(*) FROM emp;

# TRUNCATE TABLE and ROLLBACK, autocommit is true by default
INSERT INTO emp SELECT * FROM employees;  # copy data
SELECT COUNT(*) FROM emp;  
SET autocommit = FALSE;
TRUNCATE FROM emp;	        ### rollback here, because truncate is committed anyway.
SELECT COUNT(*) FROM emp;
ROLLBACK;
SELECT COUNT(*) FROM emp;