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. |