TRUNCATE vs DELETE vs DROP in SQL

Introduction

These commands help you remove data or tables from a database quickly and safely.

When you want to remove all rows from a table but keep the table structure.

When you want to delete specific rows from a table.

When you want to completely remove a table and its data from the database.

Syntax

SQL

TRUNCATE TABLE table_name;

DELETE FROM table_name WHERE condition;

DROP TABLE table_name;

TRUNCATE removes all rows fast but keeps the table ready for new data.

DELETE removes rows one by one and can use conditions to remove specific rows.

Examples

Removes all rows from the 'employees' table quickly.

SQL

TRUNCATE TABLE employees;

Deletes only employees who work in the Sales department.

SQL

DELETE FROM employees WHERE department = 'Sales';

Deletes the entire 'employees' table and all its data.

SQL

DROP TABLE employees;

Sample Program

This example shows how DELETE removes one row, TRUNCATE removes all rows fast, and DROP removes the whole table.

SQL

CREATE TABLE fruits (id INT, name VARCHAR(20));
INSERT INTO fruits VALUES (1, 'Apple'), (2, 'Banana'), (3, 'Cherry');

-- Delete one row
DELETE FROM fruits WHERE name = 'Banana';

-- Show remaining rows
SELECT * FROM fruits;

-- Remove all rows fast
TRUNCATE TABLE fruits;

-- Show rows after truncate
SELECT * FROM fruits;

-- Drop the table
DROP TABLE fruits;

Important Notes

TRUNCATE is faster than DELETE because it does not log individual row deletions.

DELETE can use WHERE to remove specific rows; TRUNCATE cannot.

DROP removes the table structure and data; use it carefully.

Summary

TRUNCATE clears all rows quickly but keeps the table.

DELETE removes rows selectively and logs each deletion.

DROP deletes the entire table and its data permanently.