DELETE with WHERE clause in MySQL - Step-by-Step Execution

Concept Flow - DELETE with WHERE clause

Start DELETE command

↓

Check WHERE condition on each row

↓

Delete row

↓

Repeat for all rows

↓

End DELETE command

The DELETE command checks each row against the WHERE condition. If the condition is true, the row is deleted; otherwise, it stays.

Execution Sample

MySQL

DELETE FROM employees WHERE department = 'Sales';

Deletes all rows from the employees table where the department is 'Sales'.

Execution Table

Step	Row ID	department	WHERE condition (department='Sales')	Action
1	1	Sales	True	Delete row 1
2	2	HR	False	Keep row 2
3	3	Sales	True	Delete row 3
4	4	Marketing	False	Keep row 4
5	5	Sales	True	Delete row 5
6	-	-	-	End of table, DELETE complete

💡 All rows checked; rows with department 'Sales' deleted.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Table rows count	5	4	4	3	3	2	2

Key Moments - 2 Insights

Why are only some rows deleted and not all?

Only rows where the WHERE condition is true are deleted, as shown in execution_table rows 1, 3, and 5. Rows with 'False' condition remain.

What happens if the WHERE clause is missing?

Without WHERE, all rows would be deleted. Here, the WHERE clause limits deletion to matching rows only.

Visual Quiz - 3 Questions

Look at the execution table, what happens at step 2?

A Row 2 is deleted

B Row 2 is kept

C Row 2 is updated

D Row 2 is ignored

💡 Hint

Check the 'Action' column for step 2 in the execution_table.

At which step does the DELETE command finish checking all rows?

A Step 4

B Step 5

C Step 6

D Step 3

💡 Hint

Look for the row with 'End of table, DELETE complete' in the execution_table.

If the WHERE clause was removed, how would the variable 'Table rows count' change after step 1?

A It would decrease to 0

B It would stay the same

C It would increase

D It would decrease by 1

💡 Hint

Without WHERE, the condition is always true, so step 1 deletes row 1 like in the table, decreasing the count by 1.

Concept Snapshot

DELETE FROM table_name WHERE condition;
- Deletes rows matching the condition.
- Rows not matching stay unchanged.
- Without WHERE, all rows are deleted.
- Always check condition to avoid unwanted deletions.

Full Transcript

The DELETE command with a WHERE clause checks each row in the table. If the row meets the condition, it is deleted; otherwise, it remains. This example deletes employees in the Sales department. The execution table shows each row checked and action taken. The variable tracker shows the table row count decreasing as rows are deleted. Key points include understanding that only matching rows are deleted and that omitting WHERE deletes all rows. The visual quiz tests understanding of these steps.