COALESCE for NULL handling in SQL - Time & Space Complexity

Time Complexity: COALESCE for NULL handling

O(n)

Understanding Time Complexity

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

Specifically, how does checking for NULL values with COALESCE affect performance?

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT id, COALESCE(phone, alternate_phone, 'N/A') AS contact_number
FROM customers;

This query selects each customer's ID and returns the first non-NULL phone number or 'N/A' if none exist.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database checks each row in the customers table once.
• How many times: Once per row, applying COALESCE to the phone columns.

How Execution Grows With Input

As the number of customers grows, the database must check more rows.

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

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

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 makes the query slower exponentially because it checks multiple columns."

[OK] Correct: COALESCE checks a fixed number of columns per row, so the work per row stays constant, not growing with data size.

Interview Connect

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

Self-Check

"What if we added a JOIN to another large table before applying COALESCE? How would the time complexity change?"