What is the difference between DELETE, TRUNCATE and DROP?

Options

1. They are identical commands and only their names are different
2. DELETE removes chosen rows; TRUNCATE all rows; DROP the table (Correct option)
3. DROP removes only a single row from the table that is named
4. TRUNCATE deletes the whole database, not only one table

Answer

DELETE removes selected rows (can use WHERE, logged per row); TRUNCATE quickly removes all rows but keeps the table; DROP removes the entire table and its structure

Why

• DELETE FROM t WHERE ...: removes the rows you choose. Can be rolled back in a transaction, fires triggers, slower on big tables.
• TRUNCATE TABLE t: empties the table very fast (no WHERE), resets auto-increment counters; in many databases it is DDL and harder to roll back.
• DROP TABLE t: deletes the table itself, with structure, indexes and data.

Always test destructive statements inside a transaction or on a copy first.

Remember

DELETE some rows, TRUNCATE all rows, DROP the table.

Key terms

• DDL / DML
• SQL
• SELECT / WHERE / HAVING / ORDER BY
• Transaction