What is the difference between DELETE, TRUNCATE, and DROP?

Question

Accepted Answer

10
   What is the difference between DELETE, TRUNCATE, and DROP?
  Level: Basic — One of the most frequently asked MySQL questions; easy to confuse under pressure.

  All three remove data, but they operate at different levels. In short: DELETE removes rows, TRUNCATE empties the whole table, and DROP removes the entire table structure itself.

  
     DELETE (DML): Removes rows one by one, can use a WHERE clause, and can be rolled back. Fires triggers.
     TRUNCATE (DDL): Removes all rows instantly by deallocating data pages. Cannot be rolled back and does not fire triggers.
     DROP (DDL): Deletes the entire table — structure, data, indexes, and constraints — from the database.
  

  
     Common trap: TRUNCATE resets the AUTO_INCREMENT counter back to its start value, while DELETE keeps it as-is. Also, TRUNCATE is a DDL command, not DML.
  

   Side-by-Side Comparison
  
    
      
        
          Feature
           DELETE
           TRUNCATE
           DROP
        
      
      
        
          Command type
          DML
          DDL
          DDL
        
        
          Removes
          Specific / all rows
          All rows
          Entire table
        
        
          WHERE clause
          Yes
          No
          No
        
        
          Rollback possible?
          Yes
          No
          No
        
        
          Resets AUTO_INCREMENT
          No
          Yes
          N/A
        
        
          Fires triggers
          Yes
          No
          No
        
        
          Speed
          Slow
          Fast
          Fast
        
        
          Table structure remains?
          Yes
          Yes
          No
        
      
    
  

   Quick Example
-- DELETE: remove specific rows (can rollback)
DELETE FROM employees WHERE dept_id = 10;

-- DELETE: remove all rows but keep structure & auto_increment
DELETE FROM employees;

-- TRUNCATE: remove all rows fast, reset auto_increment
TRUNCATE TABLE employees;

-- DROP: remove the whole table completely
DROP TABLE employees;

  
     Interviewer tip: The one-liner they want — "DELETE removes rows (rollback-able), TRUNCATE removes all rows and resets the counter, and DROP removes the entire table structure."

Feature	DELETE	TRUNCATE	DROP
Command type	DML	DDL	DDL
Removes	Specific / all rows	All rows	Entire table
WHERE clause	Yes	No	No
Rollback possible?	Yes	No	No
Resets AUTO_INCREMENT	No	Yes	N/A
Fires triggers	Yes	No	No
Speed	Slow	Fast	Fast
Table structure remains?	Yes	Yes	No

What is the difference between DELETE, TRUNCATE, and DROP?

Answer with Explanation

What is the difference between DELETE, TRUNCATE, and DROP?

Side-by-Side Comparison

Quick Example