NULLIF function behavior in PostgreSQL - Time & Space Complexity

Time Complexity: NULLIF function behavior

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 the function behave when used on many rows in a table?

Scenario Under Consideration

Analyze the time complexity of the following SQL query using NULLIF.

SELECT id, NULLIF(value, 0) AS result
FROM numbers;
    

This query returns each row's id and replaces the value with NULL if it equals zero.

Identify Repeating Operations

Look for repeated actions in the query.

• Primary operation: Applying NULLIF to each row's value.
• How many times: Once for every row in the table.

How Execution Grows With Input

As the number of rows increases, the number of times NULLIF runs also increases.

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.

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: "NULLIF runs only once regardless of rows."

[OK] Correct: NULLIF is applied to each row separately, so it runs as many times as there are rows.

Interview Connect

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

Self-Check

"What if we replaced NULLIF with a more complex function that does multiple checks? How would the time complexity change?"