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 itself

How it works

  1. DELETE FROM order_items WHERE order_id = 110 removes 2 of the 14 rows; 12 remain.
  2. TRUNCATE would remove all 14 rows instantly and keep the empty table (SQLite has no TRUNCATE; DELETE without WHERE does the same job).
  3. DROP TABLE order_items removes the table completely: querying it afterwards is an error.
  4. The catalog (the database's list of its own tables) shows the table is gone.

Key points

Common mistakes

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.