LearnSQL DELETE: Removing Rows vs TRUNCATE TABLE

18 / 61 0%

Lesson 18 of 61

5 mins read

SQL DELETE: Removing Rows vs TRUNCATE TABLE

In Plain English

The `DELETE` statement removes existing rows from a table based on a `WHERE` condition. To wipe an entire table cleanly, `TRUNCATE TABLE` is a fast DDL command that deallocates data pages instantly rather than deleting rows one by one.

The Mental Model

"`DELETE` with `WHERE` is using an eraser to remove specific names from a chalkboard. `TRUNCATE TABLE` is wiping the entire chalkboard clean with a sponge in one swipe."

Deep Dive: How It Works

DELETE vs TRUNCATE: `DELETE` logs individual row deletions, fires triggers, and obeys foreign keys; `TRUNCATE` is a fast DDL operation that resets identity counters and drops pages.

Soft Deletes vs Hard Deletes: Production applications often use soft deletes (`UPDATE users SET deleted_at = NOW()`) rather than permanently destroying records with `DELETE`.

Cascading Deletions: If foreign keys specify `ON DELETE CASCADE`, deleting a parent customer automatically deletes all their child orders.

Syntax Blueprint

DELETE FROM table_name
WHERE condition_to_remove;

TRUNCATE TABLE table_name;  -- Wipe all rows fast

Always supply a WHERE clause with DELETE to prevent wiping the entire table.

Core Rules to Remember

WHERE is Mandatory: `DELETE FROM table;` deletes every row in the table.

Foreign Key Integrity: Databases prevent deleting parent rows referenced by existing child foreign keys.

Soft Deletes: Consider boolean or timestamp flags (is_deleted = 1) for audit trails and recovery.

Common Beginner Traps & How to Fix Them

Mistake:Writing `DELETE * FROM table` (confusing with SELECT *).

Why it happens: Syntax error: DELETE operates on whole rows, so `*` is invalid.

How to fix: Write `DELETE FROM table_name WHERE ...`.

Pro Tip: In PostgreSQL, add `RETURNING *` to a DELETE statement to capture and inspect the exact deleted rows before they vanish.

Live Interactive Example

Hit Run Code to see it live

Targeted Row Deletion with DELETE

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The banned record was permanently deleted while active accounts remained intact.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Delete Inactive Records

Delete rows from `sessions` where `active = 0`.
Select all remaining rows.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.