I had to learn from this mistake more than once in my SQL career, until it became a kind of reflex: always think about the NULL values when you are writing a NOT IN subquery. Well, you should ALWAYS keep the NULL values in mind as a SQL developer, but in this case the consequences can be really nasty! And it drives me crazy how many times I’ve seen this error in my consulting life, even in production code. So, what is the point here?

Let’s start with an example using a good old schema SCOTT and find out who of the employees are not managers of someone else?

SQL> select empno, ename 
  2  from   scott.emp
  3  where  empno not in (select mgr 
  4                       from   scott.emp);
no rows selected

Listing 1: Who is not a manager?

What?! Everyone is a manager? What a perfect company! Well, because of the question, the wrong result is so obvious in this case that one would immediately start looking for the error. But what if the empty set might well be expected as a result? That might not make you alert, and so the query could lead to a hard-to-find bug in your code!

But why does it happen? Everybody who starts with relational databases and SQL learns quite early about a special handling of NULL values. And you learn that equality comparison doesn’t work with them – the result cannot be TRUE or FALSE but it is NULL again. And what kind of comparison do we have using IN or NOT IN? Looking in SQL-92 standard (yes, it is sufficient here 🙂 ) one could see that IN is equivalent to =ANY and NOT IN is the same as NOT(expr IN (...)) or !=ALL. So we are dealing with equality / inequality comparisons and thus have to expect an issue comparing NULL’s. Consider Listing 2 (I’m on Oracle 23c, so that I don’t need to select FROM DUAL anymore 🙂 ): queries #1 and #2 are identical, the same is with #3 and #4. The query #5 shows the problem: as soon as a NULL value appears in the list the comparison returns NULL as well and the WHERE condition will no longer match. Now, it doesn’t matter, whether we have a list of values with NULL’s or a subquery returning some NULL’s among other values

--query #1
SQL> select 'true' where 1 in (1,2,3);
true
--query #2
SQL> select 'true' where 1 = SOME (1,2,3);
true
--query #3
SQL> select 'true' where 5 NOT IN (1,2,3);
true
--query #4
SQL> select 'true' where 5 != ALL (1,2,3);
true
--query #5
SQL> select 'true' where 5 != ALL (1,2,3,NULL);
no rows selected

Listing 2: IN/NOT IN are just equality comparisons without special NULL handling

Now, what does it mean for our example? Let’s look for a NULL value making a trouble. It is president KING, who doesn’t have a manager:

SQL> select empno, ename, mgr
  2  from   scott.emp
  3  where  mgr is null;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7839 KING                 

When I’m about to write a NOT IN subquery, I often treat it as an additional quality check for the data model. Think about columns you’re selecting in it. Are they mandatory in your model and just missing a NOT NULL constraint defined on them? If so you’d better fix this by defining this NOT NULL constraint, which is important for many reasons. Well, in our case, the column MGR should remain nullable to be able to reflect the KING’s case. So just add the condition to exclude all NULL’s from your subquery!

SQL> select empno, ename 
  2  from   scott.emp
  3  where  empno not in (select mgr 
  4                       from   scott.emp
  5                       where  mgr is not null );

     EMPNO ENAME     
---------- ----------
      7876 ADAMS     
      7521 WARD      
      7499 ALLEN     
      7900 JAMES     
      7369 SMITH     
      7934 MILLER    
      7654 MARTIN    
      7844 TURNER    

8 rows selected. 

And of course, having the NULL on the other side of comparison, will lead to a NULL result as well, consider:

SQL> select 'true' where null not in (1,2,3);
no rows selected

But I think it is somehow more expected outcome in this case, isn’t it? What does it mean for our example? Notice that the data model is not waterproof. For instance, there is no foreign key constraint on the column MGR, so we can update it to a non-existing value of EMPNO. If we then had to query such rows which have an invalid value as MGR, would the query fail completely as well just because of the KING’s record having NULL as MGR? Indeed, this will work! Of course, those rows were MGR is a NULL, such as KING, will not be selected, but all others will be checked and returned as appropriate:

SQL> update scott.emp 
  2  set    mgr = -1
  3  where  empno = 7369;

1 row updated.

SQL> 
SQL> select empno, ename , mgr
  2  from   scott.emp
  3  where  mgr not in (select empno 
  4                     from   scott.emp )   ;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH              -1

My motivation for this post was that I once again had to write a NOT IN subquery with a nullable column. And this post is really the beginner level. But there is much more to to tell here. You may ask:

• What if I rewrite the query as NOT EXISTS?
• Or even as a LEFT JOIN?
• How does the database execute them?
• Is the performance significantly different?
• What is subquery unnesting and what does is mean for performance considerations?
• What are Null-Aware Anti-Joins in Oracle and do / how they help?
• Should I always use the particular query shape for such kind of questions? (Spoiler: “always” is most ALWAYS the wrong word to use in the IT context 😉 )

I think it is better to cover these questions in the next blog post (or even a couple of them)