17.10 - Behavior of Nulls for NOT IN - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

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.