Why CASE expressions are needed in SQL - Performance Analysis

Time Complexity: Why CASE expressions are needed

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a SQL query changes when we use CASE expressions.

Specifically, we ask: How does adding CASE affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following SQL snippet using CASE.

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

This query classifies each employee's salary into categories using CASE.

Identify Repeating Operations

Look for repeated work done by the database.

• Primary operation: Checking each employee's salary against conditions in CASE.
• How many times: Once for every employee row in the table.

How Execution Grows With Input

As the number of employees grows, the database checks more salaries.

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

Pattern observation: The work grows directly with the number of employees.

Final Time Complexity

Time Complexity: O(n)

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

Common Mistake

[X] Wrong: "CASE expressions make queries much slower because they add many checks."

[OK] Correct: Each row is checked once, so the extra work is small and grows linearly, not exponentially.

Interview Connect

Understanding how CASE expressions affect query time helps you explain your SQL choices clearly and confidently.

Self-Check

"What if we added nested CASE expressions inside the main CASE? How would the time complexity change?"