In which scenario will a NOT IN clause produce unexpected results?

Question

SQL Fundamentals — Hard

In which scenario will a NOT IN clause produce unexpected results?

Accepted Answer

When the subquery in a NOT IN clause returns any NULL values, the entire condition evaluates to unknown, resulting in no rows being returned. This unexpected behavior occurs because NULL values are not directly comparable in SQL.