DELETE with WHERE condition in SQL - Step-by-Step Execution

Concept Flow - DELETE with WHERE condition

Start DELETE command

↓

Check WHERE condition on each row

↓

Mark row

↓

Delete marked rows

↓

End

The DELETE command checks each row against the WHERE condition. Rows that match are deleted; others stay.

Execution Sample

SQL

DELETE FROM Employees WHERE Department = 'Sales';

Deletes all rows from Employees table where Department is 'Sales'.

Execution Table

Step	Row ID	Department	WHERE Condition (Department='Sales')	Action
1	1	HR	False	Keep row
2	2	Sales	True	Mark for deletion
3	3	IT	False	Keep row
4	4	Sales	True	Mark for deletion
5	5	Marketing	False	Keep row
6	-	-	-	Delete rows marked for deletion (Row IDs 2,4)
7	-	-	-	End of DELETE command

💡 All rows checked; rows with Department='Sales' are deleted.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Rows in Employees	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,2:Sales,3:IT,4:Sales,5:Marketing]	[1:HR,3:IT,5:Marketing]

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 2 and 4. Rows with False condition remain.

What happens if the WHERE condition is missing?

Without WHERE, all rows would be deleted. Here, the WHERE condition filters rows to delete only those matching 'Sales'.

Visual Quiz - 3 Questions

Look at the execution table, which row IDs are deleted?

A 1 and 3

B 2 and 4

C 3 and 5

D All rows

💡 Hint

Check the 'Action' column where rows are marked for deletion.

At which step does the DELETE command remove rows?

A Step 4

B Step 5

C Step 6

D Step 7

💡 Hint

Look for the step mentioning 'Delete rows marked for deletion'.

If the WHERE condition was Department = 'IT', which rows would be deleted?

A Row 3 only

B Rows 1 and 5

C Rows 2 and 4

D No rows

💡 Hint

Refer to the 'Department' values in the variable_tracker and execution_table.

Concept Snapshot

DELETE FROM table_name WHERE condition;
- Checks each row against condition.
- Deletes only rows where condition is true.
- Without WHERE, deletes all rows.
- Use carefully to avoid data loss.

Full Transcript

The DELETE command removes rows from a table that meet a specific condition given by WHERE. It checks each row one by one. If the condition is true, that row is deleted. If false, the row stays. For example, DELETE FROM Employees WHERE Department = 'Sales' deletes only employees in Sales. The execution table shows each row checked and action taken. Rows 2 and 4 match and are deleted. Rows 1, 3, and 5 do not match and remain. This selective deletion helps avoid removing all data accidentally. Always use WHERE to target specific rows.

Practice

1. What does the DELETE statement with a WHERE condition do in SQL?

easy

A. Removes only the rows that match the condition specified in WHERE.

B. Removes all rows from the table regardless of the condition.

C. Updates the rows that match the condition.

D. Creates a new table with rows matching the condition.

Solution

1. Step 1: Understand DELETE statement

   The DELETE statement removes rows from a table.

2. Step 2: Effect of WHERE condition

   When a WHERE condition is present, only rows matching that condition are deleted.

3. Final Answer:

   Removes only the rows that match the condition specified in WHERE. -> Option A

4. Quick Check:

   DELETE with WHERE removes matching rows only [OK]

Hint: DELETE with WHERE deletes matching rows only, not all [OK]

Common Mistakes:

• Thinking DELETE always removes all rows
• Confusing DELETE with UPDATE
• Assuming WHERE creates a new table

2. Which of the following is the correct syntax to delete rows where the column age is greater than 30 from a table named employees?

easy

A. REMOVE FROM employees WHERE age > 30;

B. DELETE employees WHERE age > 30;

C. DELETE FROM employees WHERE age > 30;

D. DELETE * FROM employees WHERE age > 30;

Solution

1. Step 1: Identify correct DELETE syntax

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

2. Step 2: Check WHERE clause usage

   The WHERE clause filters rows to delete; it must follow the table name.

3. Final Answer:

   DELETE FROM employees WHERE age > 30; -> Option C

4. Quick Check:

   DELETE FROM + table + WHERE condition [OK]

Hint: Use DELETE FROM table WHERE condition; to delete specific rows [OK]

Common Mistakes:

• Omitting FROM keyword
• Using REMOVE instead of DELETE
• Adding * after DELETE which is invalid

3. Given the table products with rows:
{id:1, name:'Pen', stock:10}, {id:2, name:'Pencil', stock:0}, {id:3, name:'Eraser', stock:5}
What will be the result of this query?

DELETE FROM products WHERE stock = 0;

medium

A. Deletes all products because stock = 0 is ignored.

B. Deletes the product with id 2 only.

C. Deletes no rows because stock = 0 is false for all.

D. Causes a syntax error.

Solution

1. Step 1: Identify rows matching condition

   Only the product with stock = 0 is the one with id 2 (Pencil).

2. Step 2: Effect of DELETE with WHERE

   DELETE removes only rows matching the condition, so only id 2 is deleted.

3. Final Answer:

   Deletes the product with id 2 only. -> Option B

4. Quick Check:

   DELETE WHERE stock=0 removes matching rows only [OK]

Hint: DELETE removes only rows matching WHERE condition [OK]

Common Mistakes:

• Assuming all rows are deleted
• Thinking condition is ignored
• Expecting syntax error for valid query

4. What is wrong with this SQL statement?

DELETE FROM orders WHERE;

medium

A. It updates rows instead of deleting.

B. It deletes all rows because WHERE is empty.

C. It creates a new table instead of deleting.

D. Missing condition after WHERE causes syntax error.

Solution

1. Step 1: Check syntax after WHERE

   WHERE clause must have a condition; empty WHERE is invalid syntax.

2. Step 2: Result of missing condition

   SQL parser throws syntax error because condition is missing.

3. Final Answer:

   Missing condition after WHERE causes syntax error. -> Option D

4. Quick Check:

   WHERE must have a condition, else syntax error [OK]

Hint: WHERE must always have a condition, never empty [OK]

Common Mistakes:

• Leaving WHERE empty thinking it deletes all
• Confusing DELETE with UPDATE
• Expecting query to run without error

5. You have a table users with columns id, name, and status. You want to delete all users whose status is either 'inactive' or 'banned'. Which query correctly deletes these users?

hard

A. DELETE FROM users WHERE status IN ('inactive', 'banned');

B. DELETE FROM users WHERE status = 'inactive' OR 'banned';

C. DELETE FROM users WHERE status = 'inactive' AND status = 'banned';

D. DELETE FROM users WHERE status = 'inactive', 'banned';

Solution

1. Step 1: Understand condition for multiple values

   To delete rows where status is 'inactive' or 'banned', use OR or IN clause.

2. Step 2: Compare options

   DELETE FROM users WHERE status = 'inactive' OR 'banned'; -- OR 'banned' (literal string) evaluates to true, deleting all rows.
   DELETE FROM users WHERE status IN ('inactive', 'banned'); -- correctly matches either value.
   DELETE FROM users WHERE status = 'inactive' AND status = 'banned'; -- AND condition is impossible, deletes none.
   DELETE FROM users WHERE status = 'inactive', 'banned'; -- invalid comma syntax.

3. Final Answer:

   DELETE FROM users WHERE status IN ('inactive', 'banned'); -> Option A

4. Quick Check:

   Use WHERE status IN (...) for multiple values [OK]

Hint: Use WHERE column IN (values) to delete multiple matches [OK]

Common Mistakes:

• Using AND instead of OR for multiple conditions
• Incorrect syntax with commas in WHERE
• Not using IN for multiple values