What is the difference between DELETE, TRUNCATE, and DROP?
All three remove data, but they operate at very different levels.
DELETE — removes rows matching a
WHEREclause. DML, transactional, can be rolled back, fires triggers, slower (row by row).
TRUNCATE — empties the entire table. DDL, faster (deallocates pages), resets
AUTO_INCREMENT, can't useWHERE, no triggers.
DROP — removes the entire table itself, including its structure, indexes, and data. DDL, irreversible (without backup).
-- DELETE: remove specific rows (can rollback, fires triggers)
DELETE FROM orders WHERE created_at < '2023-01-01';
-- Table still exists with remaining rows
-- TRUNCATE: remove ALL rows instantly (resets auto-increment)
TRUNCATE TABLE logs;
-- Table still exists but is empty, auto-increment resets to 1
-- DROP: remove the entire table
DROP TABLE IF EXISTS temp_data;
-- Table no longer exists at all
-- Safe deletion with transaction
START TRANSACTION;
DELETE FROM users WHERE id = 42;
-- Oops, wrong user!
ROLLBACK; -- Undo the delete
-- Delete with JOIN
DELETE orders FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.status = 'deleted';DELETE with WHERE removes specific rows and supports ROLLBACK. TRUNCATE removes everything instantly and resets AUTO_INCREMENT. DROP removes the table definition entirely.
IF EXISTS prevents errors when the table does not exist. The transaction example shows rollback capability of DELETE. DELETE with JOIN removes rows based on related table conditions.
DELETE = DML (rows, rollback, triggers, WHERE). TRUNCATE = DDL (all rows, fast, resets auto-increment, no triggers). DROP = DDL (entire table gone).
This is one of the most asked SQL interview questions. Know all three differences.