DELETE, DROP and TRUNCATE are SQL commands used to remove data or database objects, but they work differently.
- DELETE removes specific rows from a table and can be used with a WHERE clause.
- TRUNCATE removes all rows from a table while keeping the table structure intact.
- DROP removes the entire table, including its data and structure.
The table below shows the major differences between DELETE, TRUNCATE and DROP:
| DELETE | TRUNCATE | DROP |
|---|---|---|
| Removes selected rows from a table. | Removes all rows from a table. | Removes the entire table. |
| Can be used with a WHERE clause. | Cannot be used with a WHERE clause. | No WHERE clause is required. |
| Table structure remains intact. | Table structure remains intact. | Table structure is deleted. |
| Generally slower for removing large amounts of data. | Generally faster for removing all rows. | Removes the table completely. |
| Is a DML command. | Is a DDL command. | Is a DDL command. |
| Can remove specific rows based on a condition. | Removes all records at once. | Removes the table and all its records. |
| Example: DELETE FROM Students WHERE id = 5; | Example: TRUNCATE TABLE Students; | Example: DROP TABLE Students; |
Example of DELETE, TRUNCATE and DROP
The following examples demonstrate how DELETE, TRUNCATE and DROP work and how they affect the table.
Query:
DELETE FROM employee
WHERE employee_id = 5;
Output:
Query:
TRUNCATE TABLE employee;Output:
Query:
DROP TABLE employee;Output:
