CASE WHEN expression in MySQL - Step-by-Step Execution

Concept Flow - CASE WHEN expression

Start

↓

Evaluate CASE expression?

No→Evaluate WHEN conditions in order

↓

Check WHEN condition 1

↓

True?

Yes→Return THEN result 1

↓

Compare CASE value

↓

Match found?

No→Check next WHEN condition

↓

No WHEN matched?

↓

Return THEN result

↓

End

The CASE WHEN expression checks conditions in order and returns the THEN result of the first true condition or ELSE if none match.

Execution Sample

MySQL

SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

This query assigns grades A, B, or C based on the score for each student.

Execution Table

Step	Row (name, score)	Condition Checked	Condition Result	Action	Output (grade)
1	(Alice, 95)	score >= 90	True	Return 'A'	A
2	(Bob, 85)	score >= 90	False	Check next WHEN
3	(Bob, 85)	score >= 80	True	Return 'B'	B
4	(Charlie, 75)	score >= 90	False	Check next WHEN
5	(Charlie, 75)	score >= 80	False	Check next WHEN
6	(Charlie, 75)	No more WHEN	N/A	Return ELSE 'C'	C
7	End of rows	N/A	N/A	Query complete

💡 All rows processed; CASE returned first matching THEN or ELSE result.

Variable Tracker

Variable	Start	After Alice	After Bob	After Charlie	Final
grade	NULL	A	B	C	C

Key Moments - 2 Insights

Why does the CASE stop checking conditions after the first true WHEN?

Because CASE returns the THEN result immediately when a WHEN condition is true, as shown in rows 1 and 3 of the execution_table.

What happens if none of the WHEN conditions are true and there is no ELSE?

CASE returns NULL if no WHEN matches and ELSE is missing, similar to row 6 but with ELSE replaced by NULL.

Visual Quiz - 3 Questions

Look at the execution_table, what grade does Bob get at step 3?

A 'A'

B 'B'

C 'C'

D NULL

💡 Hint

Check the Output (grade) column at step 3 in the execution_table.

At which step does the CASE expression return the ELSE result?

A Step 2

B Step 4

C Step 6

D Step 7

💡 Hint

Look for the row where 'Return ELSE' is the action in the execution_table.

If Alice's score was 85 instead of 95, what would her grade be?

A 'B'

B 'A'

C 'C'

D NULL

💡 Hint

Refer to how Bob's score 85 results in grade 'B' in the execution_table.

Concept Snapshot

CASE WHEN expression syntax:
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Checks conditions in order, returns first THEN result where condition is true, else ELSE or NULL.

Full Transcript

The CASE WHEN expression in SQL evaluates conditions one by one. For each row, it checks the first WHEN condition; if true, it returns the corresponding THEN result immediately. If false, it moves to the next WHEN. If no WHEN conditions are true, it returns the ELSE result if provided, or NULL otherwise. This flow ensures only one result per row. The example query assigns grades based on score ranges. The execution table shows step-by-step how each row's score is checked and what grade is assigned. Variables track the grade assigned after each row. Key moments clarify why CASE stops after the first true condition and what happens if ELSE is missing. The quiz tests understanding of these steps and outcomes.

Practice

1. What does the CASE WHEN expression do in a MySQL query?

easy

A. It creates a new table in the database.

B. It sorts the results in ascending order.

C. It deletes rows that meet a condition.

D. It returns different values based on specified conditions.

Solution

1. Step 1: Understand the purpose of CASE WHEN

   The CASE WHEN expression checks conditions and returns values accordingly.

2. Step 2: Compare with other options

   Creating tables, deleting rows, or sorting are different SQL operations, not done by CASE WHEN.

3. Final Answer:

   It returns different values based on specified conditions. -> Option D

4. Quick Check:

   CASE WHEN = conditional value return [OK]

Hint: CASE WHEN returns values based on conditions, like IF-THEN-ELSE [OK]

Common Mistakes:

• Thinking CASE WHEN creates or deletes tables
• Confusing CASE WHEN with ORDER BY sorting
• Assuming CASE WHEN changes data permanently

2. Which of the following is the correct syntax for a simple CASE WHEN expression in MySQL?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' FROM students;

D. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END students;

Solution

1. Step 1: Check the correct CASE WHEN syntax

   The correct syntax includes CASE WHEN condition THEN result ELSE result END.

2. Step 2: Identify syntax errors in other options

   The other options miss keywords or punctuation: some omit WHEN, others forget END, or misplace FROM.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; -> Option A

4. Quick Check:

   Correct CASE WHEN syntax = SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; [OK]

Hint: CASE WHEN needs WHEN, THEN, ELSE, and END keywords [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END at the end
• Misplacing FROM clause

3. Given the table employees with a column salary, what will this query return?

SELECT name, CASE WHEN salary >= 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. An error because CASE WHEN cannot be used in SELECT.

B. Only employees with salary greater than 5000 will be shown.

C. A list of employee names with 'High' or 'Low' based on salary threshold 5000.

D. A list of salaries labeled as 'High' or 'Low' without employee names.

Solution

1. Step 1: Understand the CASE WHEN usage in SELECT

   The query labels each employee's salary as 'High' if >= 5000, else 'Low'.

2. Step 2: Check what columns are selected

   It selects employee names and the computed salary_level, so all employees appear.

3. Final Answer:

   A list of employee names with 'High' or 'Low' based on salary threshold 5000. -> Option C

4. Quick Check:

   CASE WHEN labels salaries, all employees listed [OK]

Hint: CASE WHEN in SELECT returns labels per row without filtering [OK]

Common Mistakes:

• Thinking CASE WHEN filters rows instead of labeling
• Assuming query returns error
• Ignoring selected columns

4. Identify the error in this query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM customers;

medium

A. Missing WHEN keyword after CASE.

B. Missing END keyword at the end.

C. Incorrect alias usage for CASE expression.

D. Using ELSE without THEN is invalid.

Solution

1. Step 1: Review CASE WHEN syntax

   CASE must be followed by WHEN before condition; here WHEN is missing.

2. Step 2: Check other parts of the query

   END keyword is present, alias is correct, ELSE is properly used after THEN.

3. Final Answer:

   Missing WHEN keyword after CASE. -> Option A

4. Quick Check:

   CASE requires WHEN after CASE [OK]

Hint: Always write CASE WHEN, not CASE condition directly [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE and THEN order
• Forgetting END keyword

5. You want to categorize products by price into 'Cheap' (price < 20), 'Moderate' (20 ≤ price ≤ 100), and 'Expensive' (price > 100). Which CASE WHEN expression correctly implements this?

hard

A. CASE WHEN price < 20 THEN 'Cheap' WHEN price < 100 THEN 'Moderate' ELSE 'Expensive' END

B. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

C. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

D. CASE WHEN price <= 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

Solution

1. Step 1: Analyze the price ranges needed

   'Cheap' is price less than 20, 'Moderate' is price between 20 and 100 inclusive, 'Expensive' is above 100.

2. Step 2: Check each option's conditions

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END correctly uses price < 20 for 'Cheap', price >= 20 and <= 100 for 'Moderate', else 'Expensive'. Others have incorrect boundaries.

3. Final Answer:

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END -> Option B

4. Quick Check:

   Correct range checks = CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END [OK]

Hint: Use inclusive ranges carefully with AND and >=, <= operators [OK]

Common Mistakes:

• Overlapping or missing ranges
• Using incorrect comparison operators
• Not covering all possible values