DELETE removes rows from a table. Like UPDATE, it is dangerous if you forget the WHERE clause.
Basic DELETE
DELETE FROM students WHERE name = 'Unknown';One name deleted. The rest of the table is untouched.
DELETE All Rows
Without WHERE, DELETE removes everything but keeps the table structure:
DELETE FROM students;
The table still exists. It is just empty. This is different from DROP TABLE,
which removes the table entirely.
DELETE vs TRUNCATE
Some databases have a TRUNCATE command that does the same thing as
DELETE FROM table but faster. SQLite does not support TRUNCATE,
but in MySQL and PostgreSQL, TRUNCATE is generally preferred when you want
to clear an entire table because it uses fewer resources.
A Word of Caution
I once watched a colleague run DELETE FROM orders without a WHERE clause
on a production database. It was not a good day. Some databases let you wrap deletes
in a transaction so you can roll back if something goes wrong:
BEGIN TRANSACTION;
DELETE FROM students WHERE id = 99;
-- Check if you deleted the right thing
SELECT * FROM students;
-- If everything is fine:
COMMIT;
-- If not:
-- ROLLBACK;Getting into the habit of using transactions before destructive operations will save you one day. Trust me on this.