Simple CASE syntax in SQL - Step-by-Step Execution

Concept Flow - Simple CASE syntax

Start CASE expression

↓

Evaluate expression value

↓

Compare with WHEN values

↓

Match found?

No→Check next WHEN

↓

Return THEN result

↓

No matches

Yes→Return ELSE result or NULL

↓

End CASE expression

The CASE expression evaluates a value, compares it to WHEN values, returns the matching THEN result, or ELSE if no match.

Execution Sample

SQL

SELECT
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    ELSE 'Needs Improvement'
  END AS performance
FROM students;

This query assigns a performance label based on the grade value for each student.

Execution Table

Step	grade value	Compare with WHEN	Match?	Result returned
1	'A'	'A'	Yes	'Excellent'
2	'B'	'A'	No	Check next WHEN
2	'B'	'B'	Yes	'Good'
3	'C'	'A'	No	Check next WHEN
3	'C'	'B'	No	Check next WHEN
3	'C'	ELSE	N/A	'Needs Improvement'

💡 All rows processed; CASE returns matching THEN or ELSE if no match.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
grade	N/A	'A'	'B'	'C'	N/A
Result	N/A	'Excellent'	'Good'	'Needs Improvement'	N/A

Key Moments - 2 Insights

Why does the CASE stop checking WHEN conditions after a match?

Because once a WHEN condition matches the expression value, CASE returns the THEN result immediately, as shown in execution_table rows 1 and 3.

What happens if none of the WHEN values match the expression?

CASE returns the ELSE result if provided, or NULL if ELSE is missing, as shown in execution_table row 6 for grade 'C'.

Visual Quiz - 3 Questions

Look at the execution_table, what result is returned when grade is 'B'?

A 'Excellent'

B 'Good'

C 'Needs Improvement'

D NULL

💡 Hint

Check execution_table row 3 where grade 'B' matches WHEN 'B'.

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

A Step 3

B Step 1

C Step 2

D No ELSE result returned

💡 Hint

See execution_table rows 5 and 6 for grade 'C' with no WHEN match.

If the ELSE clause was removed, what would be the result for grade 'C'?

A 'Good'

B 'Needs Improvement'

C NULL

D Error

💡 Hint

Without ELSE, CASE returns NULL when no WHEN matches, as explained in key_moments.

Concept Snapshot

Simple CASE syntax:
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END
- Evaluates expression once
- Compares to WHEN values
- Returns matching THEN result or ELSE
- ELSE is optional; returns NULL if missing

Full Transcript

The Simple CASE syntax in SQL evaluates an expression and compares it to multiple WHEN values. If a match is found, it returns the corresponding THEN result immediately. If no match is found, it returns the ELSE result if provided, or NULL otherwise. This process stops as soon as a match is found. For example, if the grade is 'A', the CASE returns 'Excellent'. If the grade is 'C' and no WHEN matches, it returns 'Needs Improvement' from ELSE. This helps assign labels or categories based on a single value efficiently.