Why equals NULL fails in SQL - Performance Analysis

Time Complexity: Why equals NULL fails

O(n)

Understanding Time Complexity

When we check if a value equals NULL in SQL, the way the database processes this affects how long it takes. We want to understand how the time to run such a query changes as the data grows.

How does the database handle comparisons with NULL, and what does that mean for performance?

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT *
FROM employees
WHERE department_id IS NULL;
    

This query tries to find employees whose department_id is NULL using the correct IS NULL operator.

Identify Repeating Operations

Look at what the database does repeatedly when running this query.

• Primary operation: It checks each row's department_id to see if it IS NULL.
• How many times: Once for every row in the employees table.

How Execution Grows With Input

As the number of rows grows, the database checks more rows.

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 run the query grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "Using '=' to compare with NULL works like normal values and returns matching rows."

[OK] Correct: In SQL, NULL means unknown, so '=' never returns true when comparing to NULL. This makes the query return no rows, even though it checks every row.

Interview Connect

Understanding how NULL comparisons work and their impact on query time helps you write correct and efficient SQL. This skill shows you know how databases handle special values and how that affects performance.

Self-Check

"What if we replace '=' with 'IS NULL' in the WHERE clause? How would the time complexity change?"