DELETE vs TRUNCATE vs DROP in SQL: the differences, with examples

"What is the difference between DELETE, TRUNCATE and DROP?" is one of the first SQL questions interviewers ask, because the three commands look similar and behave very differently. The short version: DELETE removes some rows, TRUNCATE empties the table, and DROP removes the table itself.

This guide runs all three on a small table. The statements ran in DuckDB, and each output is exactly what it printed. The differences in what each command can do are the same in every database, apart from the notes marked in the text.

The table

A small app_logs table with 6 rows from two services.

CREATE TABLE app_logs (
  log_id  INTEGER,
  service VARCHAR,
  level   VARCHAR,
  message VARCHAR
);

INSERT INTO app_logs VALUES
  (1, 'web', 'INFO',  'started'),
  (2, 'web', 'ERROR', 'timeout'),
  (3, 'api', 'INFO',  'started'),
  (4, 'api', 'ERROR', 'bad request'),
  (5, 'web', 'INFO',  'request ok'),
  (6, 'api', 'INFO',  'request ok');

SELECT COUNT(*) AS rows_in_table FROM app_logs;

rows_in_table
-------------
6
(1 row)

1DELETE: remove the rows you choose

DELETE removes rows, and WHERE decides which. The table stays.

DELETE FROM app_logs WHERE level = 'INFO' AND service = 'web';

SELECT * FROM app_logs ORDER BY log_id;

log_id | service | level | message
-------+---------+-------+------------
2      | web     | ERROR | timeout
3      | api     | INFO  | started
4      | api     | ERROR | bad request
6      | api     | INFO  | request ok
(4 rows)

The two web INFO rows are gone and four remain. If you forget the WHERE, DELETE FROM app_logs removes every row, so many people write the WHERE first and run it as a SELECT before changing it to a DELETE. DELETE also works 1 row at a time behind the scenes and writes each removal to a log, which is why it is slower than TRUNCATE on a very large table, but it is also why it can be undone.

2DELETE inside a transaction can be rolled back

A transaction is a group of statements that either all succeed or all get undone. Start one, delete, look, and then change your mind:

BEGIN TRANSACTION;
DELETE FROM app_logs WHERE service = 'api';

SELECT COUNT(*) AS rows_inside_transaction FROM app_logs;

rows_inside_transaction
-----------------------
1
(1 row)

ROLLBACK;

SELECT COUNT(*) AS rows_after_rollback FROM app_logs;

rows_after_rollback
-------------------
4
(1 row)

Inside the transaction the api rows were gone, and after ROLLBACK they were back. This ability to undo is the safety net that makes DELETE the careful choice.

3TRUNCATE: empty the whole table, fast

TRUNCATE removes every row at once and keeps the empty table with its columns. It has no WHERE, so it cannot pick rows.

TRUNCATE app_logs;

SELECT COUNT(*) AS rows_after_truncate FROM app_logs;

rows_after_truncate
-------------------
0
(1 row)

Zero rows, but the table still exists and you can insert into it again. TRUNCATE is much faster than DELETE on large tables, because instead of removing rows one by one it resets the table's storage in a single step. In some databases it also resets the automatic counter of an auto-incrementing id back to 1.

4DROP: remove the table itself

DROP TABLE removes the rows and the table: its columns, indexes and constraints are all gone. Afterwards the table does not exist.

DROP TABLE app_logs;

SELECT COUNT(*) FROM app_logs;

Catalog Error: Table with name app_logs does not exist!

The error is the proof: there is nothing left to query. To use the table again you must create it from scratch. DROP is a structure command. Whether it can be rolled back depends on the database: PostgreSQL and SQL Server can undo it inside a transaction, while MySQL and Oracle cannot. Treat it as permanent.

5The differences side by side

DML and DDL are labels interviewers like. DML (data manipulation language) changes the data: INSERT, UPDATE, DELETE. DDL (data definition language) changes the structure: CREATE, ALTER, DROP, and in most databases TRUNCATE.

Which one should I use?

• Remove some rows, or you might need to undo it: DELETE with a careful WHERE.
• Empty a table you will reuse, such as a staging table before a fresh load: TRUNCATE.
• You no longer need the table at all: DROP.

One more rule that protects you: if other tables point at this one with foreign keys, the database may refuse DELETE or TRUNCATE until you remove those rows first, as shown in primary key vs foreign key. In a real job, run any destructive command on a copy or inside a transaction first, and check the row count before you commit to it.

Practise the related comparison in UNION vs UNION ALL counts, a free problem, or revisit SQL for beginners if WHERE is still new.