DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP

Step 1 Understand it

Why you need it. "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.

Watch it happen

Picture it

flowchart LR
  D["DELETE … WHERE<br/>DML · chosen rows<br/>can roll back"] --> T["TRUNCATE<br/>all rows, fast<br/>table stays"] --> X["DROP<br/>table, rows,<br/>indexes: all gone"]

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.

The shape of it

DELETE FROM t WHERE condition;   -- DML: chosen rows
TRUNCATE TABLE t;                -- DDL: all rows, fast
DROP TABLE t;                    -- DDL: the table itself

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

DELETE FROM order_items WHERE order_id = 110;
SELECT COUNT(*) AS items_left FROM order_items;

DROP TABLE order_items;
SELECT name
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

Must-know 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.

In other databases

• SQLite has no TRUNCATE; DELETE FROM t; without WHERE is optimised to do the same thing.
• PostgreSQL: TRUNCATE … RESTART IDENTITY CASCADE resets numbering and empties referencing tables too.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL DELETE Statement

  short page with a try-it editor, free

• Read

  PostgreSQL docs — TRUNCATE

  official documentation, free

• Practice

  SQLBolt — Lesson 18: Dropping tables

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain DELETE, TRUNCATE and DROP with the filing-cabinet analogy, then technically.

   Show answer

   With a filing cabinet: DELETE takes out the files you choose, one at a time. TRUNCATE tips the whole drawer into the bin but keeps the drawer. DROP throws away the cabinet itself.

   Technically:
   • DELETE is DML. It removes the rows matching a WHERE (or all rows without one), row by row, logging each one and firing triggers. It can be rolled back.
   • TRUNCATE is DDL. It empties the whole table in one fast step, with no WHERE, and usually resets the auto-numbering. Whether it can be rolled back depends on the database (yes in PostgreSQL and SQL Server, no in MySQL and Oracle).
   • DROP is DDL. It removes the table completely: rows, columns, indexes, constraints and permissions. Querying it afterwards is an error.

2. Writefrom a blank page, then run it

   Delete all cancelled orders and their items (items first, because of the foreign key), then count what is left in both tables.

   Show answer

   Delete the children first, then the parents: the foreign key from order_items to orders would refuse it the other way round. Wrapping both in one transaction means you never end up with half the clean-up done.

   The only cancelled order is 106, and it has no items, so 9 orders and all 14 item lines remain.

   sqlCopy

   BEGIN;
   DELETE FROM order_items
   WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'cancelled');
   DELETE FROM orders WHERE status = 'cancelled';
   COMMIT;
   SELECT (SELECT COUNT(*) FROM orders) AS orders_left,
          (SELECT COUNT(*) FROM order_items) AS items_left;

   Try it

   1 row

   orders_left	items_left
   9	14

3. Predictsay it before you run it

   After DELETE FROM products;, how many rows are in products — and does the statement succeed? (Think about order_items.)

   Show answer

   It fails, and products still has all 7 rows. order_items points at products 1 to 6, so deleting those products would leave item lines pointing at nothing: "FOREIGN KEY constraint failed". A statement either succeeds completely or not at all, so not even the phone case (which no item uses) is deleted.

   sqlCopy

   DELETE FROM products;

   Try it

   Error: FOREIGN KEY constraint failed

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Which family does each belong to: GRANT, SAVEPOINT, ALTER, SELECT, TRUNCATE?

   Show answer

   • GRANT: DCL (Data Control Language): it gives a permission.
   • SAVEPOINT: TCL (Transaction Control Language): it marks a point inside a transaction you can roll back to.
   • ALTER: DDL (Data Definition Language): it changes a table's structure.
   • SELECT: DML (Data Manipulation Language). Some books give it its own family, DQL (Data Query Language).
   • TRUNCATE: DDL. It removes rows, but it works on the table as a whole, not row by row, which is why it is DDL and not DML.

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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.

Follow-up questions

Tick each check when you can do it without notes