What is the difference between DELETE and TRUNCATE commands when working with database tables?

Main differences between DELETE and TRUNCATE commands when working with database tables:

• DELETE removes rows from a table based on a condition (if specified), or all rows if no condition is given. This is a DML (Data Manipulation Language) operation.

  • You can use WHERE for selective deletion.
  • Deletion occurs row by row, which can be slower.
  • The operation is logged, allowing rollback.

• TRUNCATE removes all rows from a table entirely. This is a DDL (Data Definition Language) operation.

  • Does not support WHERE — deletes all data.
  • Works faster because it does not delete rows one by one, but resets data.
  • Usually does not log each row deletion, so rollback may be limited.
  • Can reset auto-increment counters.

Example:

DELETE FROM users WHERE id = 10; -- deletes a specific row
TRUNCATE TABLE users; -- deletes all rows from the table

The choice depends on the task: if you need to delete all data quickly — TRUNCATE, if selectively — DELETE.