DROP & TRUNCATE

DELETE is not the only way to get rid of data. SQL gives you two more heavyweight tools — DROP TABLE and TRUNCATE TABLE — that each remove things at a different scale. Knowing exactly what each one does (and does not do) is essential before you ever run one against a real database.

DROP TABLE — remove the table entirely

DROP TABLE deletes the table’s structure and all of its data, permanently. The columns, constraints, indexes, and rows are all gone — it is as if the table never existed.

Dropping a table

SQL

DROP TABLE employees;

Most databases also support a safer variant that avoids an error if the table is already gone:

Guarding against a missing table

SQL

DROP TABLE IF EXISTS employees;

TRUNCATE TABLE — remove all rows, keep the table

TRUNCATE TABLE deletes every row in a table but leaves the table structure — its columns, constraints, and indexes — intact and ready to receive new data.

Emptying a table

SQL

TRUNCATE TABLE order_logs;

Why TRUNCATE is faster than DELETE

DELETE removes rows one at a time and logs each individual row removal so it can be rolled back and so triggers can fire per row. TRUNCATE instead deallocates the data pages the table uses in one operation, without logging individual rows. For clearing an entire large table, TRUNCATE is typically dramatically faster than an unfiltered DELETE.

Comparing DELETE, TRUNCATE, and DROP

Operation	Removes rows	Removes structure	Can use WHERE?	Resets auto-increment?	Can be rolled back?	Speed
DELETE	Yes (selectively)	No	Yes	No	Yes	Slowest
TRUNCATE	Yes (all rows)	No	No	Usually yes	Depends on database	Fast
DROP	Yes (all rows)	Yes	No	N/A — table is gone	Depends on database	Fast

Dialect differences

In PostgreSQL, both TRUNCATE and DROP participate in transactions and can be rolled back like any other statement. In MySQL, TRUNCATE and DROP each implicitly commit the current transaction, so they cannot be undone with ROLLBACK. Always check your specific database before relying on rollback as a safety net.

When to use which

Use DELETE when you need to remove a subset of rows based on a condition, or when you need row-level triggers to fire
Use TRUNCATE when you want to empty an entire table quickly and don't need a WHERE clause
Use DROP when you no longer need the table at all — for example, cleaning up a temporary staging table or removing a deprecated feature's schema

All three are dangerous

DELETE without a WHERE clause, TRUNCATE, and DROP are among the most destructive statements in SQL. There is often no undo once a transaction commits. Always double-check you are connected to the right database, confirm the table name, and consider taking a backup before running any of them against production data.