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?"

Practice

1. What does the SQL function COALESCE do when used in a query?

easy

A. Converts all values to NULL

B. Counts the number of NULL values in a column

C. Deletes rows with NULL values

D. Returns the first non-NULL value from the given list of expressions

Solution

1. Step 1: Understand the purpose of COALESCE

   COALESCE checks each value in order and returns the first one that is not NULL.

2. Step 2: Compare with other options

   Counting NULLs, deleting rows, or converting values to NULL are not what COALESCE does.

3. Final Answer:

   Returns the first non-NULL value from the given list of expressions -> Option D

4. Quick Check:

   COALESCE = first non-NULL value [OK]

Hint: COALESCE picks the first value that is not NULL [OK]

Common Mistakes:

• Thinking COALESCE counts NULLs
• Confusing COALESCE with DELETE
• Assuming COALESCE converts values to NULL

2. Which of the following is the correct syntax to use COALESCE in SQL?

easy

A. COALESCE(column1, column2, 'default')

B. COALESCE(column1; column2; 'default')

C. COALESCE(column1 column2 'default')

D. COALESCE[column1, column2, 'default']

Solution

1. Step 1: Recall COALESCE syntax

   COALESCE uses parentheses and commas to separate arguments: COALESCE(value1, value2, ...).

2. Step 2: Identify correct punctuation

   Only COALESCE(column1, column2, 'default') uses commas and parentheses correctly; others use semicolons, spaces, or brackets incorrectly.

3. Final Answer:

   COALESCE(column1, column2, 'default') -> Option A

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for COALESCE arguments [OK]

Common Mistakes:

• Using semicolons instead of commas
• Omitting commas between arguments
• Using square brackets instead of parentheses

3. Given the table Employees with columns FirstName, MiddleName, and LastName, what will this query return?

SELECT FirstName, COALESCE(MiddleName, 'N/A') AS Middle, LastName FROM Employees;

Assuming one row has FirstName='John', MiddleName=NULL, LastName='Doe'.

medium

A. John | N/A | Doe

B. John | NULL | Doe

C. John | '' | Doe

D. John | MiddleName | Doe

Solution

1. Step 1: Understand COALESCE behavior on NULL

   COALESCE returns the first non-NULL value. Since MiddleName is NULL, it returns 'N/A'.

2. Step 2: Apply to the given row

   FirstName is 'John', MiddleName replaced by 'N/A', LastName is 'Doe'.

3. Final Answer:

   John | N/A | Doe -> Option A

4. Quick Check:

   NULL replaced by 'N/A' using COALESCE [OK]

Hint: COALESCE replaces NULL with first non-NULL value [OK]

Common Mistakes:

• Expecting NULL instead of replacement
• Confusing empty string with NULL
• Thinking COALESCE returns column name

4. Identify the error in this SQL query:

SELECT COALESCE(FirstName MiddleName, 'Unknown') FROM Employees;

medium

A. COALESCE cannot have string literals

B. Missing comma between FirstName and MiddleName

C. COALESCE requires only one argument

D. Missing FROM clause

Solution

1. Step 1: Check COALESCE argument syntax

   Arguments must be separated by commas. Here, FirstName and MiddleName are missing a comma.

2. Step 2: Verify other parts

   String literals are allowed, multiple arguments allowed, and FROM clause is present.

3. Final Answer:

   Missing comma between FirstName and MiddleName -> Option B

4. Quick Check:

   Arguments need commas in COALESCE [OK]

Hint: Separate COALESCE arguments with commas [OK]

Common Mistakes:

• Forgetting commas between arguments
• Thinking COALESCE only takes one argument
• Ignoring syntax errors in argument list

5. You have a table Orders with columns OrderID, Discount, and SpecialDiscount. Some rows have NULL in both discount columns. You want to write a query that returns OrderID and the first available discount value, or 0 if both are NULL. Which query correctly uses COALESCE to achieve this?

hard

A. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders;

B. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders;

C. SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders;

D. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders;

Solution

1. Step 1: Understand the requirement

   Return the first non-NULL discount from Discount or SpecialDiscount, or 0 if both are NULL.

2. Step 2: Analyze each option

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; uses COALESCE with three arguments correctly. SELECT OrderID, COALESCE(Discount + SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums discounts before COALESCE, which is wrong if both are NULL. SELECT OrderID, COALESCE(Discount, 0) + COALESCE(SpecialDiscount, 0) AS FinalDiscount FROM Orders; sums COALESCE results, which adds discounts instead of picking first non-NULL. SELECT OrderID, COALESCE(Discount, SpecialDiscount) AS FinalDiscount FROM Orders; misses the default 0.

3. Final Answer:

   SELECT OrderID, COALESCE(Discount, SpecialDiscount, 0) AS FinalDiscount FROM Orders; -> Option C

4. Quick Check:

   COALESCE with fallback 0 picks first non-NULL or 0 [OK]

Hint: Use COALESCE with all options and default last [OK]

Common Mistakes:

• Adding discounts instead of picking first non-NULL
• Forgetting default fallback value
• Using COALESCE incorrectly with expressions