SQL · Theory

Delete vs Truncate Command

Theory

267/439

Delete vs Truncate Command

DELETE FROM t WHERE … removes some or all rows, can ROLLBACK in a transaction, fires DELETE triggers, does not reset AUTO_INCREMENT by itself (usually). TRUNCATE empties the table fast, resets AUTO_INCREMENT, typically cannot roll back like DELETE, no per-row delete triggers.

DROP TABLE removes the table too. Three different words — draw a tiny table on the board and say what remains after each.

Trap: TRUNCATE when you needed WHERE. Trap: DELETE FROM t as a ‘fast empty’ on a huge InnoDB table (TRUNCATE or drop/recreate may be better in maintenance windows).

On the example next to this theory: Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Exam tip

DELETE rows, TRUNCATE empty, DROP table.

Delete vs Truncate Command — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefDELETE = rows (optional WHERE). TRUNCATE = all rows, keep structure. DROP = table gone.
RuleWHERE → DELETE. Empty + reset AI → TRUNCATE.
TrapMixing the three.

SQL · Theory

Delete vs Truncate Command

← All stacks

Theory

267/439

Delete vs Truncate Command

DROP TABLE removes the table too. Three different words — draw a tiny table on the board and say what remains after each.

Trap: TRUNCATE when you needed WHERE. Trap: DELETE FROM t as a ‘fast empty’ on a huge InnoDB table (TRUNCATE or drop/recreate may be better in maintenance windows).

On the example next to this theory: Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Exam tip

DELETE rows, TRUNCATE empty, DROP table.

Delete vs Truncate Command — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

Delete vs Truncate Command — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefDELETE = rows (optional WHERE). TRUNCATE = all rows, keep structure. DROP = table gone.
RuleWHERE → DELETE. Empty + reset AI → TRUNCATE.
TrapMixing the three.

Delete vs Truncate Command

Delete vs Truncate Command — sample query

Short notes

Questions

Delete vs Truncate Command

Delete vs Truncate Command — sample query

Short notes

Questions