← SQL roadmap

Topic 9 of 52 · Intermediate

DELETE vs TRUNCATE vs DROP

The three ways to remove data — and when each one is right.

01DELETE vs TRUNCATE vs DROP

Module 02 – Database & Table Management (DDL)

02What is it?

DELETE removes rows (optionally filtered with WHERE). TRUNCATE removes all rows but keeps the table. DROP removes the table itself.

This is one of the most frequently asked SQL interview questions.

03Why do we need it?

Picking the wrong one is costly: DROP when you meant DELETE destroys the table; DELETE on a 100-million-row staging table when TRUNCATE would do wastes hours.

04Syntax

DELETE FROM table_name [WHERE condition];
TRUNCATE TABLE table_name;
DROP TABLE table_name;

05Parameter explanation

WHERE condition
Only DELETE accepts it.
table_name
Target table in all three.

06Sample database

Every example and exercise below starts from a fresh copy of this data.

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(60) NOT NULL,
  dept_id   INT,
  salary    DECIMAL(10,2),
  hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
 (1,'Asha',10,85000,'2021-04-12'),
 (2,'Ravi',20,52000,'2022-01-03'),
 (3,'Meena',10,97000,'2019-09-20'),
 (4,'Karan',30,46000,'2023-06-15'),
 (5,'Divya',20,61000,'2020-11-30');
CREATE TABLE emp_a AS SELECT * FROM employees;
CREATE TABLE emp_b AS SELECT * FROM employees;
CREATE TABLE emp_c AS SELECT * FROM employees;

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.DELETE scans emp_a and removes the 2 Sales rows; 3 rows remain.
  2. 2.TRUNCATE empties emp_b; it exists with 0 rows.
  3. 3.DROP removes emp_c entirely; it no longer exists.

09Before and after output

Generated live by running the basic example against the sample database.

Running on the SQL engine…

10Intermediate coding example

Intermediate example

11Real-world business scenario

DELETE: remove one customer's records for a GDPR erasure request.

TRUNCATE: empty a staging table before every daily load.

DROP: remove a scratch table at the end of an ad-hoc analysis.

12Common mistakes

DELETE FROM employees; -- meant to remove one person

Missing WHERE deletes every row.

DELETE FROM employees WHERE emp_id = 4;

DROP TABLE employees; -- meant to empty it

Table and structure are gone.

TRUNCATE TABLE employees;

TRUNCATE TABLE employees WHERE salary < 50000;

No WHERE on TRUNCATE.

DELETE FROM employees WHERE salary < 50000;

13Important comparisons

FeatureDELETETRUNCATEDROP
CategoryDMLDDLDDL
RemovesSelected/all rowsAll rowsTable + rows
WHEREYesNoNo
Rollback (MySQL InnoDB)YesNoNo
Triggers fireYesNoNo
AUTO_INCREMENTKeptResetGone
Speed on large tablesSlowestFastFast

14Practical exercises

Beginner1. Remove only employee 4 from emp_a.

Reveal solution (15)
Solution · run it

DELETE with WHERE.

Beginner2. Empty emp_b but keep it.

Reveal solution (15)
Solution · run it

TRUNCATE.

Beginner3. Remove emp_c completely.

Reveal solution (15)
Solution · run it

DROP; 0 rows prove it's gone.

Intermediate4. Delete employees earning under 55000 from emp_a and show who remains.

Reveal solution (15)
Solution · run it

Ravi and Karan removed.

Intermediate5. Delete all rows from emp_a inside a transaction and roll it back.

Reveal solution (15)
Solution · run it

5 rows return.

Intermediate6. Delete emp_a rows whose department is HR using a subquery.

Reveal solution (15)
Solution · run it

Karan removed.

Advanced7. Keep only the highest-paid employee per department in emp_a.

Reveal solution (15)
Solution · run it

Correlated DELETE; 3 rows remain.

Advanced8. Produce a report showing row counts of emp_a, emp_b after DELETE (dept 10) and TRUNCATE.

Reveal solution (15)
Solution · run it

3 and 0.

16Key takeaways

  • DELETE = rows, filterable, reversible in a transaction.
  • TRUNCATE = all rows, fast, structure kept.
  • DROP = the whole table.
  • Always run a SELECT with the same WHERE before a DELETE.

17Knowledge check

1. Which can be rolled back in MySQL InnoDB?

2. Which keeps AUTO_INCREMENT counter?

3. Which fires DELETE triggers?

4. Remove inactive users only:

Interview questions

Explain DELETE vs TRUNCATE vs DROP.

DELETE is DML, removes chosen rows, logs each row, fires triggers and can roll back. TRUNCATE is DDL, removes all rows quickly, resets AUTO_INCREMENT, keeps structure, can't roll back in MySQL. DROP removes the table definition and data entirely.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “DELETE vs TRUNCATE vs DROP” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.