DELETE vs TRUNCATE vs DROP
Fast recall
Quick revision
Core answer
DELETE removes qualifying rows and can use WHERE; the table remains. TRUNCATE removes all rows through an engine-specific bulk operation with no row filter. DROP TABLE removes the table definition and its owned objects. Locking, trigger, identity, transaction, and support details differ substantially by database.Whether row removal, bulk emptying, and schema removal are separated without repeating unsafe vendor myths.
Claiming `TRUNCATE` can never be rolled back or always resets identity in every database.
Name PostgreSQL and SQLite differences and choose the operation from scope, concurrency, dependencies, and recovery needs.
Interview-ready guide
Interview answer
DELETE is a row-removal statement. A WHERE clause selects which rows to remove; omitting it removes every row while leaving the table, columns, indexes, and constraints available. Row-level delete triggers and foreign-key actions can participate according to the database.
TRUNCATE is a whole-table emptying operation with no row filter. PostgreSQL can reclaim table storage quickly, takes an ACCESS EXCLUSIVE lock, has separate truncate triggers, and can optionally restart owned identities. PostgreSQL also makes truncation transaction-safe for table data, so the common claim that it can never roll back is false there. Details differ in other products.
SQLite does not implement a TRUNCATE TABLE statement. An unqualified DELETE is the portable way to empty a SQLite table, and SQLite may apply its own truncate optimization internally. DROP TABLE goes further: it removes the table definition along with associated indexes, triggers, and constraints; dependent-object behavior and cascade syntax vary.
For example, deleting archived log rows preserves the active rows and the logs table. Dropping a temporary scratch table removes that object entirely.
DELETE FROM logs WHERE status = 'archived';
DROP TABLE scratch_data;In production I check backups, privileges, foreign-key dependencies, locks, trigger behavior, and identity requirements before a bulk operation. I never choose from a memorized speed ranking: DELETE is for row scope, TRUNCATE is an engine-specific whole-table operation, and DROP is schema removal.
DELETE vs TRUNCATE vs DROP
Direct Answer
DELETE removes qualifying rows and can use WHERE; the table remains. TRUNCATE removes all rows through an engine-specific bulk operation with no row filter. DROP TABLE removes the table definition and its owned objects. Locking, trigger, identity, transaction, and support details differ substantially by database.
Deep Dive & Architecture
Data and schema scope. These statements operate at different scopes. Delete changes a set of rows. Truncate is a database-supported bulk emptying operation. Drop removes the schema object that held the rows.
Statement behavior. DELETE evaluates its predicate and removes qualifying records while retaining the table. PostgreSQL implements TRUNCATE without scanning individual rows and applies special locking, trigger, and identity rules. SQLite accepts only the delete and drop forms here, though it can internally optimize an unfiltered delete.
Filtered log deletion. The logs table starts with two archived rows and one active row. A filtered delete removes only archived entries. A separate scratch table is dropped. The final query proves the logs schema and active row still exist, which would be impossible after dropping logs itself.
-- Expected result: [[3,"active"]]
CREATE TABLE logs (id INTEGER PRIMARY KEY, status TEXT NOT NULL);
INSERT INTO logs VALUES (1, 'archived'), (2, 'archived'), (3, 'active');
DELETE FROM logs WHERE status = 'archived';
CREATE TABLE scratch_data (id INTEGER);
DROP TABLE scratch_data;
SELECT id, status FROM logs ORDER BY id;
Vendor operational boundaries. Transaction and identity behavior differ across vendors. PostgreSQL truncation can roll back but uses strong locks and does not fire ordinary delete triggers. SQLite has no truncate statement. MySQL and other systems have their own commit and foreign-key behavior. Verify the exact engine before destructive maintenance and prefer recoverable operational procedures.
Common Mistakes & Pitfalls
Claiming TRUNCATE can never be rolled back or always resets identity in every database.
Check your understanding
Answer these without looking back at the article.
- Can PostgreSQL `TRUNCATE` be rolled back inside a transaction?
- What should SQLite use when all rows must be removed?
- Why is `DROP TABLE` more destructive than an unqualified `DELETE`?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.