CASE in WHERE clause in SQL - Time & Space Complexity

Time Complexity: CASE in WHERE clause

O(n)

Understanding Time Complexity

We want to understand how using a CASE statement inside a WHERE clause affects the time it takes to run a query.

Specifically, how does the query's work grow when the data gets bigger?

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT *
FROM orders
WHERE
  (CASE
    WHEN status = 'shipped' THEN CASE WHEN delivery_date < CURRENT_DATE THEN 1 ELSE 0 END
    ELSE CASE WHEN customer_id = 123 THEN 1 ELSE 0 END
  END) = 1;
    

This query selects orders based on a condition that changes depending on the order status.

Identify Repeating Operations

Look for repeated work done by the query.

• Primary operation: Checking each row in the orders table against the CASE condition.
• How many times: Once for every row in the table.

How Execution Grows With Input

As the number of rows grows, the database checks the CASE condition for each row.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the query takes longer in a straight line as the table gets bigger.

Common Mistake

[X] Wrong: "Using CASE in WHERE makes the query run slower than a simple condition because it's more complex."

[OK] Correct: The database still checks each row once; the CASE just changes the condition logic but does not add extra loops.

Interview Connect

Understanding how conditional logic inside queries affects performance helps you write clear and efficient database code.

Self-Check

What if we replaced the CASE with multiple OR conditions? How would that change the time complexity?