DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP
SQL commands fall into four families: DDL changes structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK). DELETE removes rows, TRUNCATE removes all rows, and DROP removes the whole table.
Why it matters
"What is the difference between DELETE, TRUNCATE and DROP?" is one of the most asked SQL theory questions. Knowing the four families answers it neatly, because the three belong to different families.
The idea
DDL (Data Definition Language) defines the shape: CREATE, ALTER, DROP, TRUNCATE.
DML (Data Manipulation Language) works on rows: SELECT, INSERT, UPDATE, DELETE.
DCL (Data Control Language) gives and takes permissions: GRANT, REVOKE.
TCL (Transaction Control Language): BEGIN, COMMIT, ROLLBACK, SAVEPOINT.
DELETE is DML: it removes rows one by one, can have a WHERE, fires triggers, and can be rolled back. TRUNCATE is DDL-like: it empties the whole table at once, very fast, with no WHERE. DROP removes the table itself — rows, structure, indexes and all.
A filing cabinet. DELETE takes out chosen files one at a time. TRUNCATE tips the whole drawer into the bin. DROP throws away the cabinet.
Bins can be searched afterwards. Whether TRUNCATE can be undone depends on the database: PostgreSQL and SQL Server can roll it back inside a transaction; MySQL and Oracle commit it immediately.
Syntax
DELETE FROM t WHERE condition; -- DML: chosen rows
TRUNCATE TABLE t; -- DDL: all rows, fast
DROP TABLE t; -- DDL: the table itselfHow it works
DELETE FROM order_items WHERE order_id = 110removes 2 of the 14 rows; 12 remain.- TRUNCATE would remove all 14 rows instantly and keep the empty table (SQLite has no TRUNCATE; DELETE without WHERE does the same job).
- DROP TABLE order_items removes the table completely: querying it afterwards is an error.
- The catalog (the database's list of its own tables) shows the table is gone.
Key points
- DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE. DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT.
- DELETE: chosen rows, WHERE allowed, row by row, fires triggers, can be rolled back.
- TRUNCATE: every row, no WHERE, very fast, usually resets auto-numbering.
- DROP: the table and everything about it.
- TRUNCATE and DROP are refused if other tables' foreign keys point at the table (unless you CASCADE).
Common mistakes
- Using TRUNCATE when you meant to keep some rows — it has no WHERE.
- Assuming TRUNCATE can always be rolled back — not in MySQL or Oracle.
- Dropping a table that views or foreign keys still depend on.
Not true: TRUNCATE can be undone with ROLLBACK in every database.
In fact: In MySQL and Oracle, TRUNCATE takes effect at once and cannot be rolled back. DELETE is the one that can be undone inside a transaction everywhere.
In the interview
SQL commands split into DDL for structure, DML for data, DCL for permissions and TCL for transactions. DELETE is DML: it removes rows matching a WHERE, logs each row, fires triggers and can be rolled back. TRUNCATE removes all rows at once — much faster, no WHERE, often resets identity counters, and in some databases can't be rolled back. DROP removes the table definition along with its data, indexes and permissions.
Part of Unit 6: Building tables and changing data in the free SQL course.