TRUNCATE command is used to remove the existing records from the table, it works the same as DELETE command.
Syntax:-
TRUNCATE TABLE table_name
Difference between DELETE and TRUNCATE Command
DELETE:- When you delete the records from the table then records will be removed but the space of records will exist in the table.
TRUNCATE:-When you truncate the records from the table then records will be removed with exists space.
Example:-
Suppose you have two records in the employees table
SELECT * FROM employees
Output:-
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | Virat | Kohli | virat@abc.com | Delhi |
| 2 | rohit | Sharma | rohit@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+
Note:- Where id is primary key with auto increment.
Example:-
FIRST CASE:- Now, You want to delete the record from DELETE Command
DELETE FROM employees
Now, check the employees table
SELECT * FROM employees
Output:- returned an empty result set
Now, Add new record into employees table
INSERT INTO employees(first_name,last_name,email,address)
VALUES ("Sachin","Tendulkar","sachin@abc.com","Mumbai");
Now, check the employees table
SELECT * FROM employees
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 3 | Sachin | Tendulkar | sachin@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+
So, This output is showing that after deleting the records from the table, space will exist so that is the reason id is started from 3.
SECOND CASE:- Now use TRUNCATE command to delete the records.
TRUNCATE TABLE employees
Now, No one record will be shown
Add new record into employees table
INSERT INTO employees(first_name,last_name,email,address)
VALUES ("Sourav","Ganguly","sourav@abc.com","Calcutta");
Now, check the employees table
SELECT * FROM employees
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | Sourav | Ganguly | sourav@abc.com | Calcutta |
+----+------------+-----------+----------------+------------+
So, This output is showing that after deleting the records through TRUNCATE Command then space also deleted so that is the reason id is started from 1.