← SQL roadmap

Topic 8 of 52 · Beginner

TRUNCATE TABLE

Remove every row instantly while keeping the table structure.

01TRUNCATE TABLE

Module 02 – Database & Table Management (DDL)

02What is it?

TRUNCATE TABLE empties a table: all rows are removed, but the table, its columns, indexes and constraints remain ready for new data. In MySQL it also resets AUTO_INCREMENT.

03Why do we need it?

Staging tables are reloaded every run. Removing millions of rows one by one with DELETE is slow and logs every row; TRUNCATE drops and recreates the data storage almost instantly.

04Syntax

TRUNCATE [TABLE] table_name;

The in-browser engine (SQLite) has no TRUNCATE, so AICodeDost translates it to DELETE FROM — rows and structure end up identical.

05Parameter explanation

table_name
Table to empty. No WHERE clause is allowed.

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');

07Basic coding example

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL takes an exclusive lock on employees.
  2. 2.Internally it drops and recreates the table's storage (DDL), so rows aren't deleted one by one.
  3. 3.AUTO_INCREMENT restarts at 1. The table still exists with 0 rows.

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

A daily job: TRUNCATE TABLE stg_transactions; then bulk-load today's file into it, validate, and merge into the main table.

12Common mistakes

TRUNCATE TABLE employees WHERE dept_id = 20;

TRUNCATE has no WHERE clause.

DELETE FROM employees WHERE dept_id = 20;

TRUNCATE TABLE departments; -- referenced by a foreign key

MySQL refuses to truncate a table referenced by an FK, even if the child is empty.

Delete with DELETE FROM, or drop the FK first.

START TRANSACTION; TRUNCATE TABLE employees; ROLLBACK;

TRUNCATE auto-commits in MySQL; ROLLBACK can't restore rows.

Use DELETE inside a transaction if you might need to undo.

13Important comparisons

TRUNCATEDELETE (no WHERE)
TypeDDLDML
Speed on big tablesVery fastSlow (row by row)
Fires DELETE triggersNoYes
Resets AUTO_INCREMENTYesNo
Can ROLLBACKNoYes

14Practical exercises

Beginner1. Empty employees and count its rows.

Reveal solution (15)
Solution · run it

0 rows; table still exists.

Beginner2. Truncate departments and show its columns still exist.

Reveal solution (15)
Solution · run it

Structure is kept.

Beginner3. Truncate employees, then insert one new row.

Reveal solution (15)
Solution · run it

Ready for new data immediately.

Intermediate4. Back up employees, truncate, and reload from the backup.

Reveal solution (15)
Solution · run it

Classic truncate-and-reload.

Intermediate5. Reload employees with only Engineering staff (dept 10).

Reveal solution (15)
Solution · run it

2 rows.

Intermediate6. Delete only Sales staff (dept 20) — use the right command.

Reveal solution (15)
Solution · run it

Filtering needs DELETE, not TRUNCATE.

Advanced7. Create a staging table, load it twice with truncation between loads, and show it holds only the last load.

Reveal solution (15)
Solution · run it

WHERE 1=0 copies structure without rows.

Advanced8. Compare row counts before and after truncation in one script.

Reveal solution (15)
Solution · run it

Reconciliation pattern: 5 then 0.

16Key takeaways

  • TRUNCATE empties a table but keeps its structure.
  • No WHERE; no DELETE triggers; resets AUTO_INCREMENT.
  • DDL in MySQL — cannot be rolled back.
  • The browser engine runs it as DELETE FROM, which gives the same visible result.

17Knowledge check

1. TRUNCATE is classified in MySQL as…

2. Can TRUNCATE take a WHERE clause?

3. After TRUNCATE in MySQL, the next AUTO_INCREMENT value is…

Interview questions

Why is TRUNCATE faster than DELETE?

DELETE removes and logs each row and fires triggers; TRUNCATE deallocates the table's data pages in one operation.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “TRUNCATE TABLE” — 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.