COALESCE for NULL handling in PostgreSQL - Time & Space Complexity

Time Complexity: COALESCE for NULL handling

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a query using COALESCE changes as the data grows.

Specifically, how does COALESCE affect the work done when checking for NULL values in a column?

Scenario Under Consideration

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

SELECT id, COALESCE(phone, 'No Phone') AS contact
FROM customers;
    

This query selects each customer's id and replaces NULL phone numbers with the text 'No Phone'.

Identify Repeating Operations

Look for repeated checks or operations done for each row.

• Primary operation: Checking if the phone column is NULL for each row.
• How many times: Once per row in the customers table.

How Execution Grows With Input

As the number of rows grows, the query checks each phone value once.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "COALESCE runs in constant time no matter how many rows there are."

[OK] Correct: COALESCE must check each row's value, so it does more work as the table grows.

Interview Connect

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

Self-Check

"What if we added a WHERE clause to filter rows before applying COALESCE? How would the time complexity change?"