What is the real difference between DELETE, TRUNCATE and DROP?
They all make rows go away, but they work at three different levels, and the level decides everything else.
DELETE works on the rows. You can give it a condition, so it can remove some of the rows and leave the rest. It works one row at a time, it fires the triggers and the row level rules for every row it removes, and it is slow on a big table. And because it is a data change, it sits inside a transaction and it can be undone with a rollback.
TRUNCATE works on the whole table at once. It empties the table, and in most engines it does so in one operation rather than one row at a time, which is why it is much faster on a big table. You cannot give it a condition in most engines, it resets the auto-numbering counter, and in some engines it counts as a schema change, so it cannot be rolled back with the rest of the transaction.
DROP works on the table itself. The table is gone, along with its columns, its indexes, its rules and any view that was built on it. To get the data back you need a backup, because there is nothing left to select from.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL
);
-- Remove some rows, keep the table, keep the structure.
DELETE FROM accounts WHERE id = 42;
-- Remove every row, keep the table and its structure.
TRUNCATE TABLE accounts;
-- Remove the table itself.
DROP TABLE accounts;
So how do you choose? It depends on what you are trying to do.
Use DELETE when you want to remove some rows and you want the transaction to be able to undo it. Use TRUNCATE when you want to empty a table completely and speed matters, such as when you are loading a batch of test data. Use DROP when the table should not exist any more, such as in a migration that replaces a table with a new shape.
The next question in this topic shows the three side by side.