DELETE FROM WHERE
The DELETE statement removes rows from a table. Like UPDATE, you should always use a WHERE clause to specify which rows to delete. Without WHERE, DELETE removes all rows from the table.
Deleted data cannot be recovered unless you have a backup or are using transactions (covered later). Always double-check your WHERE clause before executing a DELETE.
- DELETE FROM table WHERE condition: Removes specific rows that match the condition
- Always run a SELECT first to preview which rows will be deleted
- Deleted rows are gone permanently (unless inside a transaction)
Example
-- First, preview what will be deleted
SELECT * FROM users WHERE username = 'frank';
-- Delete a specific user
DELETE FROM users WHERE username = 'frank';
-- Delete users who are inactive
DELETE FROM users WHERE is_active = FALSE;
-- Delete users older than 50 who have no bio
DELETE FROM users
WHERE age > 50 AND bio IS NULL;
-- Verify the deletion
SELECT * FROM users; Notes
- In production systems, many teams use 'soft deletes' instead — setting an is_deleted column to TRUE rather than actually removing the row. This allows recovery if something was deleted by mistake.
TRUNCATE, DROP & Safety
Besides DELETE, SQL offers TRUNCATE and DROP for removing data. Each works differently and has different use cases. Understanding the differences is crucial for database safety.
TRUNCATE removes all rows from a table but keeps the table structure. DROP removes the entire table including its structure. Both are much faster than DELETE for large tables but cannot be filtered with WHERE.
- DELETE: Removes specific rows, can use WHERE, logged row by row, slower for large data
- TRUNCATE: Removes ALL rows, no WHERE clause, resets AUTO_INCREMENT, much faster
- DROP: Removes the entire table (structure + data), the table no longer exists
- DROP DATABASE: Removes an entire database and all its tables
Example
-- TRUNCATE: Remove all data but keep the table
TRUNCATE TABLE users;
-- DROP: Completely remove the table
DROP TABLE users;
-- Safely drop a table (no error if it does not exist)
DROP TABLE IF EXISTS users;
-- Drop a database
DROP DATABASE IF EXISTS my_first_db;
-- Comparison:
-- DELETE FROM users; -- Slow, logged, keeps auto_increment
-- TRUNCATE TABLE users; -- Fast, resets auto_increment
-- DROP TABLE users; -- Table is gone entirely Notes
- TRUNCATE cannot be rolled back in MySQL but can be rolled back in PostgreSQL. Always have backups before running destructive commands.