CASE WHEN expression in MySQL - Time & Space Complexity

Time Complexity: CASE WHEN expression

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a SQL query with a CASE WHEN expression changes as the data grows.

Specifically, we ask: How does the number of rows affect the work done by CASE WHEN?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

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

This query assigns a grade to each student based on their score using CASE WHEN.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The CASE WHEN expression is evaluated once for each row in the students table.
• How many times: Exactly once per row, so as many times as there are rows.

How Execution Grows With Input

As the number of rows grows, the database checks the CASE WHEN conditions for each row separately.

Input Size (n)	Approx. Operations
10	10 CASE checks
100	100 CASE checks
1000	1000 CASE checks

Pattern observation: The work grows directly with the number of rows; doubling rows doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "CASE WHEN runs only once regardless of rows because it's just a condition."

[OK] Correct: The CASE WHEN expression is checked for every row, so it runs as many times as there are rows.

Interview Connect

Understanding how CASE WHEN scales helps you explain query performance clearly and shows you know how SQL processes data row by row.

Self-Check

"What if we added a JOIN that doubles the number of rows? How would the time complexity change?"