Difference between DELETE, DROP, and TRUNCATE

Question: Difference between DELETE, DROP, and TRUNCATE SQL Commands?

Answer: Following are some of the differences between the three SQL commands:-

• DML vs DDL - The first obvious difference is that DELETE is a DML command whereas TRUNCATE and DROP both are DDL commands. As we know that we can rollback only a DML command and not a DDL command hence another inferred difference will be that DELETE can be rolled back whereas the TRUNCATE and DROP operations can not be rolled back. As with every other DML command, DELETE requires a COMMIT command to be executed to make the changes permanent, whereas TRUNCATE and DROP both being DDL don't need that. Changes will automatically get committed.
• Select Records vs All Records - DELETE can be used to delete either a select records (with WHERE clause selecting the set of records to be deleted) or all the records (either with no WHERE clause or with a WHERE clause selecting all) whereas TRUNCATE can not be used in conjunction with WHERE and it always deleted all the records of a table. DROP command not only deletes all the rows, but the entire table (or any other database object).
• Affect on Triggers, Privileges, Indexes, etc. - DELETE comand causes all the DELETE triggers on the table to be fired. TRUNCATE doesn't cause any triggers to be fired on the table and DROP also prevents any DML triggers to be fired on the table (or any other DB object). DROP command not only removes the table, but also removes all the indexes of the table, privileges defined for the table, and of course all the rows of the table and that too it removes them completely from the database. As stated above, no rollback is possible in this case.
• Performance - DELETE being a DML statement will certainly be slower than TRUNCATE (a DDL statement). One of the reasons being, DELETE needs to maintain the undo space for rollback operation, which TRUNCATE doesn't need. DROP can't really be compared with DELETE and TRUNCATE as it not only removes all the rows of a table, but also the table, its indexes and priviledges.
• Removal of spaces - DELETE command simply deletes the rows and doesn't free up that space occuppied by the deleted records. It will obviously result in fragmentation and we'll need to compact the table to get rid of it. This fragmentation makes the queries slower as the pointer navigates these free rows as well. TRUNCATE on the other hand removes the spaces occuppied by the records as well.