What are the differences between DELETE and TRUNCATE commands when deleting data from a table?

The DELETE and TRUNCATE commands are both used to remove data from a table, but they have significant differences:

• DELETE removes rows one by one, supports the WHERE condition, which allows selective deletion. The operation is logged and can be rolled back in a transaction.
• TRUNCATE removes all rows from the table completely, does not support WHERE. It usually operates faster because it does not log the deletion of each row but resets the table's data pages. TRUNCATE cannot be rolled back in some DBMSs.

Example:

DELETE FROM users WHERE status = 'inactive'; -- will only delete inactive users
TRUNCATE TABLE users; -- will delete all users at once

Also, TRUNCATE can reset the auto-increment counter, whereas DELETE cannot.