How does the function 'not in' perform with a column has null value

Database
N/A

How does the function 'not in' perform with a column has null value

I have these three sql but give me different results:

select count(*) from caestg1.zipcodes
where zip_cd not in
(select zip_cd from updatearea.zipcodes_new where zip_cd is not null);

** result returns 2380 rowcount

select count(*) from caestg1.zipcodes
where zip_cd not in
(select zip_cd from updatearea.zipcodes_new);

** result returns 0 rowcount

select count(*) from caestg1.zipcodes
where not exists
(select zip_cd from updatearea.zipcodes_new where zip_cd is not null);

** result returns 0 rowcount

could anybody explain how the null values will affect the sql result? And what is the differect between 'not in' and 'not exists' functions?

Thanks,
Ivy
1 REPLY
Teradata Employee

Re: How does the function 'not in' perform with a column has null value

you should always use WHERE COL IS NOT NULL in the sub-select when doing a NOT IN. NULLs are unknown data so NOT IN(select * from foo) will return nothing if there are nulls in the sub-select result.

EXISTS and NOT EXISTS can handle the NULL without checking for NOT NULL. The EXISTS is either TRUE or FALSE per row. The EXISTS is a correlated statement and you should code it as

select * from foo a where not exists (select * from bar b where a.col = b.col)