Difference between Delete and Truncate Command in SQL

Learn: What is SQL-Delete order and SQL – Truncate order? How to separate erase and shorten order in SQL, erase v/s shorten in SQL?

SQL – DELETE Command

• Erase/Delete is a Data Manipulation Language order.
• Here, Manipulation stands to alter/erase the records/tuples/columns.
• In the event of erasing order where the condition is discretionary methods this order works in both case whether you utilized where proviso or not, however, you should utilize where statement to erase specific records.

SQL DELETE Query: with where the provision

Show the table record:

mysql> select * from student;

Result:

Sid	Sname	Fees
102	Rahul	1000
103	Preeti	2000

3 rows in set (0.00 sec)

Presently, show the table record:

Query to DELETE:

mysql> delete from student where sid =102;

Result:

Query OK, 1 rows affected (0.06 sec)

Presently, show 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:

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 multiple times slower than shortening in light of the fact that it erases push by push (individually) from a table.
• Fundamentally erase order is well known to erase a column from a table however particularly you can erase all lines from the table on the off chance that you need.
• If there should arise an occurrence of erasing order you can rollback information after erasing proclamation since last submit.

SQL – TRUNCATE Command

• Shorten is a Data Definition Language (DDL).
• Here, Data Definition alludes to alter/evacuate table’s structure/diagram.
• If there should arise an occurrence of shortening order where proviso can’t utilize in light of the fact that it erases the whole line at a time it gives no such office to erase a solitary line from a table.

SQL Query Examples:

Show the table record:

mysql> select * from student;

Result:

Sid	Sname	Fees
102	Rahul	1000
103	Preeti	2000

3 rows in set (0.00 sec)

Inquiry 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 multiple times quicker than erase on the grounds that it erases all lines one after another from a table.
2. Fundamentally shorten order is mainstream to erase all columns at the same time from a table.
3. If there should be an occurrence of shortening order you can’t rollback information implies you can’t recoup erased information since last submit.