Differences Between DROP, TRUNCATE, and DELETE

• DELETE – A DML statement that removes rows one at a time based on an optional WHERE clause, is fully transactional and logged, and fires row-level triggers.
• TRUNCATE – A DDL statement that removes all rows at once by deallocating data pages, is much faster, usually resets identity counters, and does not fire row triggers.
• DROP – A DDL statement that removes the entire table definition along with all data, indexes, constraints, triggers, and permissions.
• Rollback – DELETE is always rollback-able; TRUNCATE is transactional in PostgreSQL but an implicit commit in MySQL; DROP is effectively irreversible without a backup.
• Destructiveness spectrum – DELETE is surgical and safe, TRUNCATE is a fast full wipe, and DROP obliterates the table entirely.

What is DELETE?

DELETE is a Data Manipulation Language (DML) statement. It removes rows that match a WHERE clause, or all rows if no WHERE is given. It operates row by row, writing each removal to the transaction log, which makes it slower on large tables but fully recoverable and rollback-able inside a transaction. Because it is logged per row, DELETE fires any row-level triggers.

-- DELETE: remove a specific erroneous transaction
BEGIN;
DELETE FROM transactions
WHERE txn_id = 'TXN-2024-ERR-001'
AND account_id = 'SAV2001';
-- 1 row deleted. Transaction log records the removal.

-- Oops, wrong transaction? Roll back.
ROLLBACK;  -- the row is restored

-- DELETE all rows (no WHERE) – slow on large tables
DELETE FROM staging_transactions;
-- Each row is individually logged. Auto-increment counter unchanged.

What is TRUNCATE?

TRUNCATE is a Data Definition Language (DDL) statement. It removes all rows by deallocating the table's data pages rather than deleting rows individually. This makes it dramatically faster and uses minimal logging. It cannot take a WHERE clause – it is all or nothing.

-- TRUNCATE: quickly empty a processed staging table
TRUNCATE TABLE staging_transactions;
-- All rows removed in one operation. Identity counter reset to 1.
-- No row-level triggers fired. No per-row log entries.

What is DROP?

DROP is DDL and goes furthest: it removes the table object entirely – all rows, the structure, indexes, constraints, triggers, and permissions. The table name no longer exists.

-- DROP: remove a deprecated legacy table
DROP TABLE IF EXISTS legacy_audit_2019;
-- Table definition, all data, indexes, constraints – everything is gone.
-- Any dependent views or foreign keys break.

How is DELETE different from TRUNCATE and DROP?

| Aspect | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Type | DML | DDL | DDL |
| WHERE clause | Yes | No (all-or-nothing) | N/A |
| Speed on large table | Slow (row by row) | Fast (page deallocation) | Fast |
| Transaction log | Full per-row logging | Minimal logging | Minimal |
| Rollback | Always rollback-able | Engine-dependent | Irreversible without backup |
| Row triggers | Fires triggers | Does not fire triggers | N/A |
| Identity counter | Unchanged | Reset to start | N/A |
| Table structure | Preserved | Preserved (empty table) | Destroyed |
| Foreign keys | Checked per row | Requires no referencing FKs | Must drop FKs first |
| Production use | Routine (with WHERE) | Staging table cleanup | Schema migration |

package com.bank.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

public class DataCleanupService {

    private static final Logger LOG = Logger.getLogger(DataCleanupService.class.getName());

    // DELETE: surgical removal of specific rows
    public int deleteErroneousTransaction(Connection conn,
                                           String txnId) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            int rows = stmt.executeUpdate(
                "DELETE FROM transactions WHERE txn_id = '" + txnId + "'");
            LOG.info("Deleted " + rows + " row(s) for txn " + txnId);
            return rows;
        }
    }

    // TRUNCATE: fast cleanup of staging table after ETL
    public void truncateStagingTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("TRUNCATE TABLE staging_transactions");
            LOG.info("Staging table truncated");
        }
    }

    // DROP: remove deprecated table (production: only via migration)
    public void dropLegacyTable(Connection conn,
                                 String tableName) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("DROP TABLE IF EXISTS " + tableName);
            LOG.warning("Dropped table " + tableName + " – irreversible!");
        }
    }
}

What about rollback behavior across databases?

Rollback behavior for TRUNCATE varies significantly across database engines. In PostgreSQL and SQL Server, TRUNCATE is transactional and can be rolled back. In MySQL with InnoDB, TRUNCATE performs an implicit commit and cannot be undone.

-- PostgreSQL: TRUNCATE is transactional
BEGIN;
TRUNCATE TABLE staging_transactions;
-- Changed our mind
ROLLBACK;  -- rows are restored in PostgreSQL

-- MySQL InnoDB: TRUNCATE is an implicit commit
START TRANSACTION;
TRUNCATE TABLE staging_transactions;
-- IMPLICIT COMMIT happens automatically. ROLLBACK has no effect.
-- The rows are gone permanently.

What about foreign key constraints?

DELETE checks foreign keys row by row and can cascade deletes to child tables if ON DELETE CASCADE is defined. TRUNCATE typically requires that no foreign keys reference the table, or that both parent and child are truncated together. DROP requires that dependent foreign keys are dropped first or that CASCADE is used.

-- DELETE with foreign key cascade
DELETE FROM customers WHERE customer_id = 42;
-- If ON DELETE CASCADE: child rows in loans, transactions also deleted
-- If ON DELETE RESTRICT: error if child rows exist

-- TRUNCATE with foreign key reference
TRUNCATE TABLE customers;  -- ERROR: cannot truncate (referenced by loans)
TRUNCATE TABLE customers CASCADE;  -- truncates customers AND loans

-- DROP with dependent foreign keys
DROP TABLE customers;  -- ERROR: loans references customers
DROP TABLE customers CASCADE;  -- drops table and cascades

What are common mistakes?

The most common mistake is using DELETE without a WHERE clause to empty a large table, which is far slower than TRUNCATE. Another mistake is assuming TRUNCATE can be rolled back on MySQL.

-- WRONG: using DELETE to empty a 100 million row staging table
DELETE FROM staging_transactions;
-- Takes minutes, fills the transaction log, locks the table

-- CORRECT: use TRUNCATE for full table cleanup
TRUNCATE TABLE staging_transactions;
-- Completes in milliseconds, minimal logging

When should you use each?

• DELETE with WHERE – Routine. Remove specific rows surgically. Fully logged and rollback-able.
• TRUNCATE – Clear staging or test tables after processing. Fast, but risky to recover.
• DROP – Schema changes in production through reviewed migrations with a backup. Irreversible.

What should a senior engineer know?

A senior engineer knows the destructiveness spectrum (DELETE is surgical, TRUNCATE is a fast wipe, DROP is obliteration), that rollback behavior varies by engine, that TRUNCATE and DROP on production data require migrations and review, and that DELETE without WHERE on a large table is a performance anti-pattern when TRUNCATE is the intent.