What is TRUNCATE TABLE?
TRUNCATE TABLE removes all rows from a table but keeps the table structure.
Think of it like emptying a box - the box stays, but everything inside is gone.
The Syntax
TRUNCATE TABLE table_name;Example
Before TRUNCATE - students table: (see table below)
students table (Before TRUNCATE)
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 21 |
3 rows
Run TRUNCATE:
TRUNCATE TABLE students;After TRUNCATE: Table is empty but still exists! (see empty table below)
students table (After TRUNCATE) - Empty!
| id | name | age |
|---|
TRUNCATE vs DELETE vs DROP
TRUNCATE - Empty the table (fast)
TRUNCATE TABLE students;
-- Table exists, but empty. IDs reset to 1.DELETE - Empty the table (slow)
DELETE FROM students;
-- Table exists, but empty. IDs continue (4, 5, 6...).DROP - Delete the entire table
DROP TABLE students;
-- Table is gone completely!Key Difference: ID Reset
DELETE - IDs continue from where they left off
-- Had IDs: 1, 2, 3
DELETE FROM products;
-- Next insert gets ID: 4TRUNCATE - IDs start fresh from 1
-- Had IDs: 1, 2, 3
TRUNCATE TABLE products;
-- Next insert gets ID: 1When to Use TRUNCATE?
- Clearing test data
- Resetting a table before importing new data
- Faster than DELETE when removing all rows
How TRUNCATE Works (Step by Step)
Step 1: Write TRUNCATE TABLE command
Step 2: Specify which table to empty
Step 3: Add semicolon ;
Step 4: All data removed, table stays!
TRUNCATE TABLE old_logs; -- All rows gone, table remainsQuick Comparison
- TRUNCATE = Fast, resets IDs, keeps table
- DELETE = Slow, keeps IDs, keeps table
- DROP = Fast, removes everything
Tip: Use TRUNCATE when you want to clear all data quickly and start fresh with ID = 1.