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
08Step-by-step execution
- 1.DELETE scans emp_a and removes the 2 Sales rows; 3 rows remain.
- 2.TRUNCATE empties emp_b; it exists with 0 rows.
- 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
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
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Category | DML | DDL | DDL |
| Removes | Selected/all rows | All rows | Table + rows |
| WHERE | Yes | No | No |
| Rollback (MySQL InnoDB) | Yes | No | No |
| Triggers fire | Yes | No | No |
| AUTO_INCREMENT | Kept | Reset | Gone |
| Speed on large tables | Slowest | Fast | Fast |
14Practical exercises
Beginner1. Remove only employee 4 from emp_a.
Reveal solution (15)
DELETE with WHERE.
Beginner2. Empty emp_b but keep it.
Reveal solution (15)
TRUNCATE.
Beginner3. Remove emp_c completely.
Reveal solution (15)
DROP; 0 rows prove it's gone.
Intermediate4. Delete employees earning under 55000 from emp_a and show who remains.
Reveal solution (15)
Ravi and Karan removed.
Intermediate5. Delete all rows from emp_a inside a transaction and roll it back.
Reveal solution (15)
5 rows return.
Intermediate6. Delete emp_a rows whose department is HR using a subquery.
Reveal solution (15)
Karan removed.
Advanced7. Keep only the highest-paid employee per department in emp_a.
Reveal solution (15)
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)
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.
Lesson test
Take an 8-question test on “DELETE vs TRUNCATE vs DROP” — concepts, output prediction, bug spotting and interview questions.