CASE in SELECT for computed columns in SQL - Step-by-Step Execution

Concept Flow - CASE in SELECT for computed columns

Start Query

↓

Read each row

↓

Evaluate CASE condition

↓

Condition True?

Yes→Return THEN value

↓

Check next WHEN or ELSE

↓

Add computed column value

↓

Return row with computed column

↓

Repeat for all rows

↓

End Query

The query reads each row, evaluates the CASE conditions to compute a new column value, then returns the row with this computed column.

Execution Sample

SQL

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

This query assigns a grade based on the score for each student.

Execution Table

Step	Row (id, score)	CASE Condition Evaluated	Condition Result	Computed Column (grade)
1	(1, 95)	score >= 90	True	'A'
2	(2, 82)	score >= 90	False	Check next WHEN
3	(2, 82)	score >= 80	True	'B'
4	(3, 75)	score >= 90	False	Check next WHEN
5	(3, 75)	score >= 80	False	Check next WHEN
6	(3, 75)	ELSE	Default	'C'
7	All rows processed	-	-	-

💡 All rows processed, CASE evaluated for each row, query ends.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
id	N/A	1	2	3	3
score	N/A	95	82	75	75
grade	N/A	'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 value of the first WHEN condition that is true, as shown in execution_table rows 1 and 3 where it stops after finding a true condition.

What happens if none of the WHEN conditions are true?

The ELSE value is used as the computed column, as shown in execution_table rows 5 and 6 for the third row where both WHEN conditions are false.

Visual Quiz - 3 Questions

Look at the execution_table, what is the computed grade for the student with id=2?

A 'B'

B 'A'

C 'C'

D NULL

💡 Hint

Check rows 2 and 3 in the execution_table where id=2 is evaluated.

At which step does the CASE expression use the ELSE clause?

A Step 2

B Step 4

C Step 6

D Step 1

💡 Hint

Look at execution_table rows 5 and 6 where both WHEN conditions are false and ELSE is applied.

If the score for id=3 was 85, what would be the computed grade?

A 'A'

B 'B'

C 'C'

D NULL

💡 Hint

Refer to the CASE conditions and see which WHEN matches score=85.

Concept Snapshot

CASE in SELECT computes new columns by checking conditions in order.
Syntax: CASE WHEN condition THEN value ELSE value END.
Stops at first true WHEN.
If none true, uses ELSE.
Useful for categorizing or transforming data in query results.

Full Transcript

This visual execution shows how a SQL query uses CASE in SELECT to compute a new column called grade based on score. Each row is read, CASE conditions are checked in order. When a condition is true, its THEN value is assigned as grade. If no WHEN is true, ELSE value is used. The execution table traces each step for three rows, showing how grades A, B, and C are assigned. Variable tracker shows how id, score, and grade change per row. Key moments clarify why CASE stops at first true condition and how ELSE works. The quiz tests understanding of these steps.