Nested CASE expressions in SQL - Step-by-Step Execution

Concept Flow - Nested CASE expressions

Start CASE

↓

Evaluate Outer CASE Condition

↓

Return

↓

Return

↓

End

The outer CASE checks a condition; if false, it evaluates an inner CASE expression before returning a result.

Execution Sample

SQL

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

Assigns grades A, B, or C based on score using nested CASE expressions.

Execution Table

Step	score	Outer CASE Condition (score >= 90)	Outer CASE Result	Inner CASE Condition (score >= 80)	Inner CASE Result	Final grade
1	95	True	'A'	N/A	N/A	'A'
2	85	False	Evaluate Inner CASE	True	'B'	'B'
3	75	False	Evaluate Inner CASE	False	'C'	'C'
4	90	True	'A'	N/A	N/A	'A'
5	80	False	Evaluate Inner CASE	True	'B'	'B'
6	70	False	Evaluate Inner CASE	False	'C'	'C'
Exit	N/A	N/A	All rows processed	N/A	N/A	Grades assigned

💡 All rows processed, final grades assigned based on nested CASE logic.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
score	N/A	95	85	75	90	80	70	N/A
Outer CASE Result	N/A	'A'	Evaluate Inner CASE	Evaluate Inner CASE	'A'	Evaluate Inner CASE	Evaluate Inner CASE	N/A
Inner CASE Result	N/A	N/A	'B'	'C'	N/A	'B'	'C'	N/A
Final grade	N/A	'A'	'B'	'C'	'A'	'B'	'C'	N/A

Key Moments - 2 Insights

Why does the inner CASE only run when the outer CASE condition is false?

Because the outer CASE returns immediately when its condition is true (rows 1 and 4), skipping the inner CASE. The inner CASE runs only when the outer CASE condition is false (rows 2,3,5,6).

What happens if the inner CASE also has multiple conditions?

The inner CASE evaluates its conditions in order, returning the first true condition's result, just like the outer CASE. This is shown in rows 2,3,5,6 where inner CASE picks 'B' or 'C'.

Visual Quiz - 3 Questions

Look at the execution table, what is the final grade when score is 85 at step 2?

A 'A'

B 'C'

C 'B'

D NULL

💡 Hint

Check row 2 in execution_table under 'Final grade' column.

At which step does the outer CASE condition become true?

A Step 1

B Step 2

C Step 3

D Step 6

💡 Hint

Look at 'Outer CASE Condition' column in execution_table for 'True' values.

If the score is 88, what would the inner CASE result be?

A 'A'

B 'B'

C 'C'

D NULL

💡 Hint

Refer to inner CASE logic and variable_tracker for scores between 80 and 89.

Concept Snapshot

Nested CASE expressions let you put one CASE inside another.
Outer CASE checks a condition first.
If false, inner CASE runs to check more conditions.
Returns the first true condition's result.
Useful for multi-level decision making in SQL.

Full Transcript

This visual execution trace shows how nested CASE expressions work in SQL. The outer CASE checks if the score is 90 or above. If yes, it returns 'A' immediately. If not, it evaluates the inner CASE which checks if the score is 80 or above to return 'B', else returns 'C'. The execution table walks through example scores step-by-step, showing which CASE runs and what grade is assigned. The variable tracker shows how values change after each step. Key moments clarify why inner CASE only runs when outer CASE condition is false and how multiple conditions are handled. The quiz tests understanding of the flow and results. The snapshot summarizes the concept simply.