delete, truncate, and drop in SQL

Question

What is the difference between: delete truncate drop in SQL?

Answer 1

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
ex.: DELETE FROM emp WHERE employee = 'SAM';

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
ex.: TRUNCATE TABLE emp;

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
ex.: DROP TABLE emp;

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Answer 2

Along with Samuel's answer I wanted to point out that if your table is using a identity key (such as an auto incrementing id) that deleting a row or even all the data does not reset that key. For example, in a table of cars if the last entry had a primary identity key of 7 and then that row is DELETEd, the next INSERT would have a key of 8 not 7. TRUNCATE would in essence reseed the table back to 1. but as was pointed out it removes all the data in the table.

Answer 3

Here a link neatly explaining DELETE, INSERT, DDL, DML... https://www.geeksforgeeks.org/sql-ddl-dml-dcl-tcl-commands/

Answer 4

I really want to thank all

Answer 5

So how do you undo the DELETE statement Samuel Mayol RobNichols