After testing, you need to clean up test data. DELETE removes specific rows. TRUNCATE removes all rows. DROP removes the entire table. Know the difference — the wrong choice can ruin your day.
| Command | What It Does | Can Undo? | WHERE Clause? | Speed |
|---|---|---|---|---|
| DELETE | Removes specific rows | Yes (with ROLLBACK) | Yes | Slower (row by row) |
| TRUNCATE | Removes ALL rows | No (in most DBs) | No | Fast (drops and recreates) |
| DROP | Removes the entire TABLE | No | N/A | Instant |
-- Delete specific test data
DELETE FROM users
WHERE email LIKE '%@test.com';
-- Delete with a more precise condition
DELETE FROM orders
WHERE user_id = 42
AND status = 'pending';
-- Delete all rows (use TRUNCATE instead for speed)
DELETE FROM temp_test_data;
-- TRUNCATE: Remove all rows instantly (no rollback!)
TRUNCATE TABLE temp_test_data;
-- DROP: Remove the entire table structure
DROP TABLE temp_test_data; -- Gone. Table structure and data.DELETE without WHERE deletes everything. Same danger as UPDATE without WHERE. The safe workflow is identical: write WHERE first, SELECT to preview, then DELETE. On production, always use transactions.
-- Cleanup script: Run after test cycle
-- Delete in reverse order of foreign key dependencies
-- Step 1: Delete child records first
DELETE FROM order_items WHERE order_id IN (
SELECT order_id FROM orders WHERE user_id IN (
SELECT id FROM users WHERE email LIKE '%@test.com'
)
);
-- Step 2: Delete parent records
DELETE FROM orders WHERE user_id IN (
SELECT id FROM users WHERE email LIKE '%@test.com'
);
-- Step 3: Delete the root records
DELETE FROM users WHERE email LIKE '%@test.com';Use a naming convention for test data. Prefix test emails with "test_" or use a dedicated domain like @autotest.local. This makes cleanup queries simple and safe — just DELETE WHERE email LIKE '%@autotest.local'.
Key Point: DELETE removes rows, TRUNCATE removes all rows fast, DROP removes the table. Always delete child records before parent records.