Answer
DELETE vs TRUNCATE vs DROP
Quick Comparison
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| What it removes | Specific rows | All rows | Entire table |
| WHERE clause | Yes | No | No |
| Rollback | Yes | Depends on DB | No (DDL) |
| Triggers | Fires triggers | No triggers | No triggers |
| Speed | Slow (logs each row) | Fast | Fast |
| Auto-increment reset | No | Yes | N/A |
| Category | DML | DDL | DDL |
DELETE — Remove Specific Rows
SQL
-- Delete specific rows
DELETE FROM employees WHERE department = 'QA';
-- Delete all rows (can be rolled back)
DELETE FROM employees;
-- With JOIN (delete based on another table)
DELETE e FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.dept_name = 'HR';
-- DELETE with LIMIT
DELETE FROM audit_logs
ORDER BY created_at ASC
LIMIT 1000; -- delete oldest 1000 rows
-- Rollback is possible
BEGIN;
DELETE FROM employees WHERE id = 5;
ROLLBACK; -- employee is restored!
TRUNCATE — Remove All Rows Fast
SQL
-- Remove ALL rows instantly
TRUNCATE TABLE employees;
-- Cannot use WHERE
-- TRUNCATE TABLE employees WHERE id = 5; ← ERROR
-- Resets AUTO_INCREMENT counter
TRUNCATE TABLE orders;
INSERT INTO orders (customer) VALUES ('Alice');
-- id will be 1 again (not continue from last value)
-- In most databases, CANNOT be rolled back
-- In PostgreSQL, TRUNCATE is transactional (can rollback)
DROP — Remove the Entire Table
SQL
-- Remove table structure + all data permanently
DROP TABLE employees;
-- Safe version — no error if table doesn't exist
DROP TABLE IF EXISTS temp_employees;
-- Drop multiple tables
DROP TABLE orders, order_items, payments;
-- Cannot be rolled back (DDL auto-commit)
-- After DROP: table is gone, INSERT will fail
When to Use Which
SQL
-- Use DELETE when:
DELETE FROM session_logs WHERE created_at < NOW() - INTERVAL 30 DAY;
-- ↑ Need WHERE clause to be selective
-- Use TRUNCATE when:
TRUNCATE TABLE test_run_results;
-- ↑ Clearing ALL test data before a test run (fast)
-- Use DROP when:
DROP TABLE IF EXISTS temp_migration_table;
-- ↑ Removing a temporary table no longer needed
In QA Automation — Database Cleanup
SQL
-- Before each test run: clean test data
TRUNCATE TABLE test_orders;
TRUNCATE TABLE test_users;
-- After specific test: clean up created record
DELETE FROM users WHERE email = 'automated_test@example.com';
-- Teardown: remove test schema entirely
DROP SCHEMA IF EXISTS test_run_20240615;