Behavior of Nulls for NOT IN

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

The following table explains the behavior of nulls for NOT IN for queries of various forms.

FOR a query of the following form … IF … THEN …
SELECT ... FROM T1 WHERE x NOT IN
(SELECT y FROM T2);
one of the y values is null no T1 rows are returned for the entire query.
some rows are returned by the subquery, and if x contains some nulls those T1 rows that contain a null in x are not returned.
SELECT ... FROM T1
WHERE expression_list_1 NOT IN
(SELECT expression_list_2 
FROM T2);
a null is the first field in expression_list_2 no rows from T1 are returned.
a null is in a field other than the first field of expression_list_2 some rows may be returned
the subquery returns some rows, and if a null is in the first field in expression_list_1 the T1 rows containing a null in the first field of expression_list_1 are not returned.
SELECT ... FROM T1WHERE expression_list_1 NOT IN
(SELECT expression_list_2 
FROM T2
WHERE search_condition);
the search_condition on T2 returns no rows all T1 rows, including those containing a NULL in the first field of expression_list_1, are returned.