NULLIF function in MySQL - Time & Space Complexity

Time Complexity: NULLIF function

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run the NULLIF function changes as the amount of data grows.

Specifically, how does checking values with NULLIF scale when used in a query?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT id, NULLIF(score, 0) AS adjusted_score
FROM results;
    

This query returns each row's id and replaces the score with NULL if the score is zero.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The NULLIF function is applied once per row in the results table.
• How many times: It runs once for each row, so if there are n rows, it runs n times.

How Execution Grows With Input

As the number of rows grows, the total number of NULLIF checks grows at the same rate.

Input Size (n)	Approx. Operations
10	10 NULLIF checks
100	100 NULLIF checks
1000	1000 NULLIF 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 in a straight line with the number of rows.

Common Mistake

[X] Wrong: "NULLIF runs once and then applies to all rows at once, so it's constant time."

[OK] Correct: NULLIF is applied separately to each row's data, so the total work depends on how many rows there are.

Interview Connect

Understanding how simple functions like NULLIF scale helps you explain query performance clearly and confidently.

Self-Check

"What if NULLIF was used inside a nested query that runs for each row of another table? How would the time complexity change?"