What are the key differences between DELETE and TRUNCATE?

DELETE:

• Removes selected records from a table.
• A WHERE condition can be used to delete specific records.
• The operation is logged row by row in the transaction log.
• ON DELETE triggers are activated.
• Slower compared to TRUNCATE.

Example:

MySQL 8.1
DELETE FROM employees WHERE salary < 30000;

TRUNCATE:

• Removes all records from a table with no possibility of recovery via ROLLBACK (in most DBMS).
• Cannot use WHERE.
• Faster since it is not logged row by row.
• Resets identity values (if auto-increment is used).
• Triggers are not activated.

Example:

MySQL 8.1
TRUNCATE TABLE employees;