Searched CASE syntax in SQL - Time & Space Complexity

Time Complexity: Searched CASE syntax

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a searched CASE statement changes as the data grows.

How does the number of rows affect the work done inside the CASE?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT
  employee_id,
  salary,
  CASE
    WHEN salary < 30000 THEN 'Low'
    WHEN salary < 70000 THEN 'Medium'
    ELSE 'High'
  END AS salary_level
FROM employees;

This code classifies each employee's salary into categories using searched CASE conditions.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The CASE conditions are checked once for each row in the employees table.
• How many times: Once per row, so as many times as there are rows.

How Execution Grows With Input

Each row requires checking the CASE conditions in order, so the total work grows with the number of rows.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "The CASE statement runs only once regardless of rows."

[OK] Correct: The CASE is evaluated for every row, so the total work depends on how many rows there are.

Interview Connect

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

Self-Check

"What if we added more WHEN conditions inside the CASE? How would the time complexity change?"