Database / SQLite Interview questions
What is the difference between DELETE, TRUNCATE, and DROP in SQLite?
SQLite doesn't actually have a TRUNCATE statement at all — that's a notable difference
from many other SQL databases. The remaining two behave distinctly.
| DELETE FROM table | DROP TABLE |
| Removes rows (optionally filtered by WHERE); the table structure remains. | Removes the entire table, including its schema, indexes, and all data. |
| Can be selective, targeting specific rows. | All-or-nothing; the table ceases to exist afterward. |
| Logged row-by-row in the rollback journal/WAL. | A schema-level operation. |
DELETE FROM person; -- removes all rows, table still exists, empty DROP TABLE person; -- table itself is gone entirely
DELETE FROM table with no WHERE clause is the closest equivalent to what other
databases call TRUNCATE, though internally it's still implemented as a row-by-row delete rather
than the specialized fast-path some other engines use for true truncation.
Does SQLite have a dedicated TRUNCATE statement?
What happens to a table's structure after DROP TABLE, compared to DELETE FROM?
More Related questions...
