The Challenge with NULL Comparisons in SQL

Read this if you've ever run into unexpected results when dealing with NULL values in SQL.

The Challenge with NULL Comparisons

Hey there! 👋 First of all, what is NULL? In SQL, NULL tells us that a value is unknown, missing, or not applicable.

⚠️ Remember, NULL IS NOT the same as zero, an empty string, or space. It’s an unknown value, a placeholder for the absence of a value.

Now let’s see what that means in terms of comparison.

Suppose we have the following 2 tables:

** Schema **
-- Customers
| customer_id | customer_name |
| ----------- | ------------- |
| 101         | Alice         |
| 104         | Bob           |
| 105         | Charlie       |

-- Orders
| order_id | customer_id |
| -------- | ----------- |
| 1        | 101         |
| 2        | 102         |
| 3        | NULL        |
| 4        | 103         |

When you compare any value to NULL using standard comparison operators (=, <>, <, >), the result isn’t TRUE or FALSE, it’s UNKNOWN.

Let’s see an example. Below, we query all orders where customer_id = NULL

SELECT * FROM Orders WHERE customer_id = NULL

-- Wow, result is empty, even though we have NULL valued customer_id.
| order_id | customer_id |
| -------- | ----------- |

As you can see, this query returns no rows, even if there are NULL values in the customer_id column. Why? The reason behind this is that the comparison customer_id = NULL yields UNKNOWN, and we know the WHERE clause only includes rows where the condition evaluates to TRUE.

NOT IN with NULL

In SQL, using the NOT IN clause can lead to unexpected results when NULL values are involved.

Suppose we are interested in finding customers who have not placed any orders. Here’s the simplest query that should help us solve the problem.

SELECT customer_id
  FROM Customers
 WHERE customer_id NOT IN (SELECT customer_id FROM Orders);

You might expect to get customer_id of 104 in the result set, because he is not in the Orders table, right?

Again, the query returns no rows.

Let’s break this weird behaviour down step by step.

1. Subquery Results: (101, 102, NULL, 103)

2. Comparison for customer_id = 104: The NOT IN clause checks if 104 is not equal to all values in the list.
   - 104 = 101 → FALSE
   - 104 = 102 → FALSE
   - 104 = NULL → UNKNOWN
   - 104 = 103 → FALSE

3. Evaluating NOT IN Clause: The condition 104 NOT IN (101, 102, NULL, 103) becomes: TRUE AND TRUE AND UNKNOWN AND TRUE. It’s equivalent to TRUE AND UNKNOWN → UNKNOWN.

4. As we mentioned above, the WHERE clause filters only the rows where the condition is TRUE. Since the condition is UNKNOWN for customer_id = 104, we don’t see it in the final set.

How to Avoid the Above Behavior

The simplest way, if you still prefer to use NOT IN clause is to exclude NULL values from the subquery:

SELECT customer_id
  FROM Customers
 WHERE customer_id NOT IN (
    SELECT customer_id FROM Orders WHERE customer_id IS NOT NULL
 );

-- Result is as expected:
| customer_id |
| ----------- |
| 104         |
| 105         |

Another way is to use NOT EXISTS clause instead:

SELECT customer_id
  FROM Customers c
  WHERE NOT EXISTS (
      SELECT 1
        FROM Orders o
       WHERE o.customer_id = c.customer_id
  );

-- Result is as expected:
| customer_id |
| ----------- |
| 104         |
| 105         |

As you might know, NOT EXISTS checks for the absence of rows that meet the condition. It doesn’t compare values to each other and avoids UNKNOWN results due to NULL comparison.

**Schema used in the above examples (PostgreSQL v15)**

    CREATE TABLE Customers (
        customer_id INT PRIMARY KEY,
        customer_name VARCHAR(50)
    );
    
    INSERT INTO Customers (customer_id, customer_name) VALUES
    (101, 'Alice'),
    (104, 'Bob'),
    (105, 'Charlie');
    
    CREATE TABLE Orders (
        order_id INT PRIMARY KEY,
        customer_id INT
    );
    
    INSERT INTO Orders (order_id, customer_id) VALUES
    (1, 101),
    (2, 102),
    (3, NULL),
    (4, 103);
    

---

I hope you liked the article. If you have questions or need further clarification, leave a comment below or reach out directly.

---

✅ Thank you for reading my article on SA Space! I welcome any questions, comments, or suggestions you may have.

Keep Knowledge Flowing by following me for more content on Solutions Architecture, System Design, Data Engineering, Business Analysis, and more. Your engagement is appreciated. 🚀

🔔 You can also follow my work on LinkedIn | Substack | My Resume