These three commands all seem to remove data and I always confuse them in theory papers. Can someone explain clearly what each one actually removes?
They differ in what they remove and whether the structure survives. DELETE removes rows, and you can use a WHERE clause to delete only some rows; the table structure stays. It is a DML command and can be rolled back. TRUNCATE removes all rows at once but keeps the empty table structure; it is faster than DELETE and cannot use WHERE. DROP removes the entire table, including its structure, columns and data, so the table no longer exists in the database. Quick way to remember: DELETE picks rows, TRUNCATE empties the table, DROP destroys the table. For exams, note that DELETE is DML while TRUNCATE and DROP are DDL commands.
Sign in as a tutor to answer this doubt.