DROP, TRUNCATE and DELETE Compared

Concept

Three statements remove data and they are constantly confused, because the difference is not in what you see afterwards but in what is left behind.

Syntax

DELETE FROM table_name WHERE condition;

TRUNCATE TABLE table_name;

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
DROP VIEW  view_name;
DROP INDEX index_name ON table_name;   -- MySQL syntax
DROP DATABASE database_name;

Example

-- Remove a subset, reversibly
START TRANSACTION;
DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2023-01-01';
-- inspect the row count, then decide
ROLLBACK;                       -- or COMMIT;

-- Empty a staging table before a fresh load
TRUNCATE TABLE staging_orders;

-- Remove an object that is no longer part of the schema
DROP TABLE IF EXISTS staging_orders_old;

Explanation

DELETE walks the rows, writes each removal to the log and fires triggers, which is why it is slow but also why it can be rolled back and audited. TRUNCATE throws away the table's data pages wholesale - far faster, but it cannot report which rows went, cannot fire row triggers and in MySQL cannot be undone because it is DDL and auto commits.

DROP removes the definition too. Afterwards the table does not exist, along with its indexes, triggers and privileges.

Important rules

• TRUNCATE is refused on a table referenced by a foreign key in MySQL, PostgreSQL and SQL Server. DELETE is allowed and will enforce or cascade the constraint.
• In MySQL and Oracle, TRUNCATE and DROP auto commit. Wrapping them in a transaction gives no protection.
• PostgreSQL allows TRUNCATE inside a transaction and can roll it back.
• TRUNCATE normally resets the auto increment or identity counter to its seed; DELETE never does.
• DROP DATABASE removes every object it contains, with no confirmation.

Common mistakes

• Running DELETE FROM orders; with no WHERE. Every row goes.
• Reaching for TRUNCATE to "clean up a bit of test data" on a table with a foreign key and getting a confusing error.
• Assuming a TRUNCATE in MySQL can be rolled back because it was typed after START TRANSACTION.
• Expecting TRUNCATE to fire an audit trigger. It does not.

Best practices

• Write the WHERE clause first, run it as a SELECT, check the row count, and only then change SELECT ... into DELETE.
• Enable safe updates while learning: SET SQL_SAFE_UPDATES = 1; in MySQL refuses an unkeyed DELETE or UPDATE.
• Take a backup before any DROP in an environment you cannot rebuild.
• Prefer a deleted_at soft delete column for business data that anyone might need to recover - exactly what this notes application itself does.

Practice

1. Which statement would you use to empty a nightly import table, and why?
2. Explain why TRUNCATE cannot be used on departments in the sample schema.
3. Write the safe two step version of delete every inactive employee hired before 2019.