Home » SQL

Difference between Delete and Truncate Command in SQL

Learn: What is SQL- Delete command and SQL – Truncate command? How to differentiate delete and truncate command in SQL, delete v/s truncate in SQL?

SQL - DELETE Command

• DELETE is a Data Manipulation Language command.
• Here, Manipulation stands to edit/delete the records/ tuples /rows.
• In case of delete command where clause is optional means this command works in both case whether you used where clause or not, but you should use where clause to delete particular records.

SQL DELETE Query: with where clause

Display the table record:

mysql> select * from student;

Result:

Sid	Sname	Fees
102	Rahul	1000
103	Preeti	2000

3 rows in set (0.00 sec)

Query to DELETE

mysql> delete from student where sid =102;

Result:

Query OK, 1 rows affected (0.06 sec)

Now, display the table record:

mysql> select * from student;

Sid	Sname	fees
103	Preeti	2000

1 row in set (0.00 sec)

SQL DELETE Query: without where clause

Display the table record:

mysql> select * from student;

Result:

Sid	Sname	Fees
102	Rahul	1000
103	Preeti	2000

3 rows in set (0.00 sec)

Query to DELETE

mysql> delete from student;

Result:

Query OK, 1 row affected (0.02 sec)

Now, display the table record:

mysql> select * from student;

Empty set (0.00 sec)

Note:

• It is 20 times slower than truncate because it delete row by row (one by one) from a table.
• Basically delete command is popular to delete a row from a table but exceptionally you can delete all rows from table if you want.
• In case of delete command you can rollback data after delete statement since last commit.

SQL - TRUNCATE Command

• Truncate is a Data Definition Language (DDL).
• Here, Data Definition refers to edit/remove table’s structure/schema.
• In case of truncate command where clause cannot use because it deletes the entire row at a time it provides no such facility to delete a single row from a table.

SQL Query Examples:

Display the table record:

mysql> select * from student;

Result:

Sid	Sname	Fees
102	Rahul	1000
103	Preeti	2000

3 rows in set (0.00 sec)

Query to TRUNCATE

mysql> truncate student;

Result:

Query OK, 2 row affected (0.02 sec)

Now, display the table record:

mysql> select * from student;

Empty set (0.00 sec)

Note:

1. It is 20 times faster than delete because it deletes all rows at a time from a table.
2. Basically truncate command is popular to delete all rows simultaneously from a table.
3. In case of truncate command you cannot rollback data means you cannot recover deleted data since last commit.