Why Does `NOT IN` Return Unexpected Results with NULL Values in SQL?

SQL's NOT IN Clause and the Nuances of NULL Values

A frequent challenge in SQL involves comparing data against a value set, especially when NULLs are present. Let's examine this with a couple of example queries:

<code class="language-sql">A: SELECT 'true' WHERE 3 IN (1, 2, 3, NULL);
B: SELECT 'true' WHERE 3 NOT IN (1, 2, NULL);</code>

Query A produces a result, but Query B does not. This behavior stems from how SQL handles NULLs within the NOT IN clause.

In SQL, NULL signifies an unknown or missing value. Its interaction with predicates like IN and NOT IN is governed by the ANSI_NULLS setting.

With ANSI_NULLS enabled (the default), comparisons involving NULL evaluate to UNKNOWN unless explicitly checked using IS NULL or IS NOT NULL.

In Query A, comparing 3 to a list containing NULL results in a TRUE evaluation because 3 is present. The NULL value doesn't affect the overall outcome.

Query B, however, uses NOT IN. Because ANSI_NULLS is on, the comparison 3 NOT IN (1, 2, NULL) evaluates to UNKNOWN due to the comparison between 3 and NULL. Consequently, the query returns an empty set.

Disabling ANSI_NULLS alters this. NULLs are treated like any other value. In this scenario, 3 NOT IN (1, 2, NULL) would evaluate to TRUE, as 3 is distinct from 1, 2, and NULL. Therefore, Query B would yield a result with ANSI_NULLS off.

This underscores the significance of the ANSI_NULLS setting when dealing with NULLs in SQL. By managing this setting, you control predicate behavior involving NULLs and obtain the desired query results.

The above is the detailed content of Why Does `NOT IN` Return Unexpected Results with NULL Values in SQL?. For more information, please follow other related articles on the PHP Chinese website!