What's the difference between DELETE, TRUNCATE, and DROP?

DELETE removes chosen rows, TRUNCATE removes every row but keeps the table, and DROP removes the table itself along with its data.

• DELETE removes the rows its WHERE clause matches, one row at a time. Delete triggers fire, the change can be rolled back, and it is slow on large tables.
• TRUNCATE empties the whole table without scanning it, so it is much faster than DELETE. It does not fire delete triggers, and MySQL and SQL Server also reset the auto-increment counter.
• DROP deletes the table definition together with its data, indexes and constraints.

Rollback depends on the engine. MySQL commits TRUNCATE and DROP immediately, so neither can be rolled back there. PostgreSQL and SQL Server can roll back a TRUNCATE inside a transaction.

DELETE FROM employees WHERE age > 65;  -- Removes specific rows
TRUNCATE TABLE employees;              -- Removes all rows, keeps table
DROP TABLE employees;                  -- Removes entire table