What are the differences between DELETE, TRUNCATE, and DROP commands when working with a database?

Main differences between DELETE, TRUNCATE, and DROP commands:

• DELETE

  • Removes rows from a table based on a condition (if no condition is specified, deletes all rows).
  • The operation is logged and can be rolled back (transactional).
  • Does not free space; the table structure remains.

• TRUNCATE

  • Quickly removes all rows from a table.
  • Usually does not log the deletion of each row; the operation cannot be rolled back in some DBMSs.
  • Resets counters (e.g., auto-increment).
  • The table structure remains.

• DROP

  • Completely deletes the entire table, including its structure and data.
  • Irreversible operation; frees all resources associated with the table.

Example:

DELETE FROM employees WHERE id = 10; -- deletes a specific row
TRUNCATE TABLE employees; -- quickly deletes all rows
DROP TABLE employees; -- completely deletes the table