If you're new here, you may want to subscribe to my Newsletter. Thanks for visiting!

Both remove records from the table, so what is the difference. Very simple, read along.

For this article, I will use a ‘friends’ table.

delete from friends;

and

truncate table friends;

Both the above statements remove all records from the table, but the essentiall difference is as follows.

delete from friends - will delete all records from the friends table. That’s it. I.e. the auto_increment counter does not get reset.

truncate table friends - will delete all records from the table and also rebuild the table, thus resetting the auto_increment counter.

Try these steps to identify yourself:

Step 1:

create table friends (
id int not null primary key auto_increment,
name varchar(20)
);

Step 2:
insert into friends (name) values (’Sunil’);
insert into friends (name) values (’Vishal’);
insert into friends (name) values (’Vikram’);

Step 3:
select * from friends;

Note the id of the last record. It will be 3.

Step 4:
delete from friends;

Step 5:
insert into friends (name) values (’Mithil’);

Step 6:
select * from friends;

Note that the id of the only record will be 4. This means that delete from friends removed all records, but did not reset the auto_increment counter.

Step 7:
Repeat Step 2 - 3 and goto step 8.

Step 8:
truncate table friends;

Step 9:
Repeat step 5 - 6 and goto step 10.

Note that after you repeat step 6, you will see that the only record i.e. of Mithil has id 1. This means that truncate deletes all the records from the table and resets the auto_increment pointer to start from 1.

Leave a comment if you need more examples or have more questions