DELETE removes rows (data) from a table, but the table structure remains.
- You can delete specific rows using
WHERE. - If you don’t use
WHERE, it will remove all rows. - You can ROLLBACK (undo) if you are using transactions.
Example:
sql
DELETE FROM students WHERE name = 'Ramesh';- Removes only Ramesh.
- Table still exists with other rows.
TRUNCATE removes all rows from a table in one go.
- You cannot use WHERE (removes everything).
- Faster than DELETE (because it doesn’t log each row).
- You cannot ROLLBACK in most databases (once gone, gone!).
- Table structure remains.
Example:
sql
TRUNCATE TABLE students;Removes all students, but the students table is still there (empty).
DROP deletes the entire table (structure + data).
- Table is gone completely.
- You cannot insert into it again unless you recreate the table.
Example:
sql
DROP TABLE students;Removes the table itself. Now students doesn’t exist anymore.
Quick Comparison Table
| Command | Deletes Data? | Deletes Table Structure? | Can Use WHERE? | Rollback Possible? |
|---|---|---|---|---|
| DELETE | ✅ Yes | ❌ No | ✅ Yes | ✅ Yes (with transaction) |
| TRUNCATE | ✅ Yes (all rows) | ❌ No | ❌ No | ❌ No (mostly) |
| DROP | ✅ Yes | ✅ Yes | ❌ No | ❌ No |
Real-Life Analogy
- DELETE → “Remove some students from the class register, but the register book remains.”
- TRUNCATE → “Erase the entire register book, but keep the book for new entries.”
- DROP → “Burn the register book completely. No book exists.”
Super Simple Memory Trick
- DELETE = Remove rows (one by one or all)
- TRUNCATE = Remove all rows (fast clean sweep)
- DROP = Remove table itself (bye-bye table)