IS NULL and IS NOT NULL in MySQL - Time & Space Complexity

Time Complexity: IS NULL and IS NOT NULL

O(n)

Understanding Time Complexity

Checking for NULL values in a database is common when filtering data.

We want to understand how the time to run these checks grows as the data size increases.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT *
FROM employees
WHERE manager_id IS NULL;

SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

This code finds all employees who do not have a manager (NULL) and those who do have a manager (NOT NULL).

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database scans each row in the employees table to check the manager_id column.
• How many times: Once per row, so as many times as there are rows in the table.

How Execution Grows With Input

As the number of employees grows, the database must check more rows for NULL or NOT NULL values.

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 check for NULL or NOT NULL grows linearly with the number of rows.

Common Mistake

[X] Wrong: "Checking for NULL is instant and does not depend on table size."

[OK] Correct: Each row must be checked individually, so more rows mean more work.

Interview Connect

Understanding how simple filters like IS NULL scale helps you explain query performance clearly and confidently.

Self-Check

"What if the column checked with IS NULL has an index? How would the time complexity change?"