NULL behavior in comparisons in SQL - Time & Space Complexity

Time Complexity: NULL behavior in comparisons

O(n)

Understanding Time Complexity

When SQL compares values, NULL behaves differently than normal values. Understanding how this affects query time is important.

We want to know how the presence of NULLs impacts the time it takes to run comparison operations.

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT *
FROM employees
WHERE salary = NULL;

-- or

SELECT *
FROM employees
WHERE salary IS NULL;
    

This code tries to find rows where the salary is NULL using two different comparison methods.

Identify Repeating Operations

Look at what happens for each row in the table.

• Primary operation: Checking the salary value for each row to see if it matches NULL.
• How many times: Once per row in the employees table.

How Execution Grows With Input

For each row, the database checks the salary column once.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the query grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "Using = NULL works the same as IS NULL in comparisons."

[OK] Correct: In SQL, = NULL never returns true because NULL means unknown. You must use IS NULL to check for NULL values correctly.

Interview Connect

Understanding how NULL behaves in comparisons shows you know how databases handle missing or unknown data, a key skill in writing correct queries.

Self-Check

"What if we replaced IS NULL with IS NOT NULL? How would the time complexity change?"