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.

Practice

1. What does the WHERE clause do in a DELETE statement in MySQL?

easy

A. It limits which rows get deleted based on a condition.

B. It specifies the table to delete from.

C. It adds new rows to the table.

D. It changes the structure of the table.

Solution

1. Step 1: Understand the role of DELETE

   The DELETE statement removes rows from a table.

2. Step 2: Understand the role of WHERE

   The WHERE clause filters which rows are affected by DELETE.

3. Final Answer:

   It limits which rows get deleted based on a condition. -> Option A

4. Quick Check:

   WHERE clause = filter rows to delete [OK]

Hint: WHERE filters rows to delete, else all rows removed [OK]

Common Mistakes:

• Thinking WHERE adds rows
• Confusing WHERE with table name
• Assuming DELETE changes table structure

2. Which of the following is the correct syntax to delete rows where the column age is less than 18 from a table named users?

easy

A. DELETE users WHERE age < 18;

B. DELETE FROM users WHERE age < 18;

C. DELETE * FROM users WHERE age < 18;

D. DELETE FROM users IF age < 18;

Solution

1. Step 1: Check DELETE syntax

   The correct syntax starts with DELETE FROM followed by the table name.

2. Step 2: Verify WHERE clause usage

   WHERE clause uses condition like age < 18 to filter rows.

3. Final Answer:

   DELETE FROM users WHERE age < 18; -> Option B

4. Quick Check:

   Correct DELETE syntax = DELETE FROM users WHERE age < 18; [OK]

Hint: Use DELETE FROM table WHERE condition; [OK]

Common Mistakes:

• Omitting FROM keyword
• Using DELETE * which is invalid
• Replacing WHERE with IF

3. Given the table employees with rows:
{id:1, name:'Alice', dept:'HR'},
{id:2, name:'Bob', dept:'IT'},
{id:3, name:'Carol', dept:'HR'}
What will be the remaining rows after running:
DELETE FROM employees WHERE dept = 'HR';

medium

A. [{id:2, name:'Bob', dept:'IT'}]

B. [{id:1, name:'Alice', dept:'HR'}, {id:3, name:'Carol', dept:'HR'}]

C. [] (empty table)

D. [{id:1, name:'Alice', dept:'HR'}, {id:2, name:'Bob', dept:'IT'}, {id:3, name:'Carol', dept:'HR'}]

Solution

1. Step 1: Identify rows matching WHERE condition

   Rows with dept = 'HR' are Alice and Carol (id 1 and 3).

2. Step 2: Delete matching rows and keep others

   Only Bob (id 2) with dept 'IT' remains after deletion.

3. Final Answer:

   [{id:2, name:'Bob', dept:'IT'}] -> Option A

4. Quick Check:

   Deleted HR rows, kept IT row [OK]

Hint: Delete rows matching condition; others stay [OK]

Common Mistakes:

• Deleting all rows by missing WHERE
• Keeping deleted rows by mistake
• Confusing which rows match condition

4. What is wrong with this query?
DELETE FROM orders WHERE;

medium

A. DELETE requires SELECT keyword.

B. DELETE cannot have WHERE clause.

C. Missing condition after WHERE clause.

D. Table name is missing.

Solution

1. Step 1: Check syntax after WHERE

   WHERE clause must have a condition to filter rows.

2. Step 2: Identify missing condition error

   Query ends with WHERE but no condition is given, causing syntax error.

3. Final Answer:

   Missing condition after WHERE clause. -> Option C

4. Quick Check:

   WHERE needs condition [OK]

Hint: Always add condition after WHERE [OK]

Common Mistakes:

• Leaving WHERE empty
• Thinking WHERE is optional but incomplete
• Confusing DELETE syntax with SELECT

5. You have a table products with columns id, name, and stock. You want to delete all products that have zero stock but keep products with NULL stock values. Which query correctly deletes only products with stock exactly zero?

hard

A. DELETE FROM products WHERE stock <= 0;

B. DELETE FROM products WHERE stock IS NULL;

C. DELETE FROM products WHERE stock != NULL;

D. DELETE FROM products WHERE stock = 0;

Solution

1. Step 1: Understand NULL behavior in WHERE

   Comparisons with NULL using =, !=, <= return UNKNOWN (false); use IS NULL to check NULL.

2. Step 2: Identify condition for stock exactly zero

   stock = 0 matches only zero, excludes NULL and other values.

3. Step 3: Analyze other options

   stock <= 0 deletes zero and negatives (keeps NULL); stock IS NULL deletes NULL stocks; stock != NULL never matches (comparison with NULL always UNKNOWN).

4. Final Answer:

   DELETE FROM products WHERE stock = 0; -> Option D

5. Quick Check:

   Use = 0 to delete zero stock only [OK]

Hint: Use = 0 to delete zero, IS NULL for nulls [OK]

Common Mistakes:

• Using <= 0 deletes negatives too
• Using != NULL deletes nothing
• Confusing NULL with zero