Lesson 22: Deleting Data (DELETE vs. TRUNCATE)

We cover two main ways to remove data from tables, both DML (DELETE) and DDL (TRUNCATE).

1. DELETE Statement (DML)

DELETE removes rows from a table based on a WHERE condition. Like UPDATE, omitting WHERE removes all rows, but it's slower than TRUNCATE because it logs every deletion (allows rollback).

Syntax

sql DELETE FROM [table_name] WHERE [condition];

Example: Deleting Inactive Accounts

Remove all customers who have not placed an order since 2020.

sql DELETE FROM Customers WHERE LastOrderDate < '2020-01-01';

2. TRUNCATE TABLE (DDL)

TRUNCATE TABLE removes all rows from a table rapidly and resets any auto-incrementing counters. It is much faster than DELETE because it deals with the data in larger chunks, but it usually cannot be rolled back.

TRUNCATE is a DDL command because it alters the state of the table structure (the counter is reset).

Syntax

sql TRUNCATE TABLE [table_name];

Key Differences

Feature	DELETE	TRUNCATE
Speed	Slower (logs row by row)	Much Faster (deallocates storage)
WHERE Clause	Yes, allows filtering	No, removes everything
Rollback	Yes (generally)	No (generally, depending on DBMS)
PK Counter Reset	No	Yes
Type	DML	DDL

Warning: Use TRUNCATE with extreme caution, as the data is usually gone permanently.