All Forums Database
ywjcjj 36 posts Joined 06/08
26 Jun 2008
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.zipcodeswhere zip_cd not in(select zip_cd from updatearea.zipcodes_new where zip_cd is not null);** result returns 2380 rowcountselect count(*) from caestg1.zipcodeswhere zip_cd not in(select zip_cd from updatearea.zipcodes_new);** result returns 0 rowcountselect count(*) from caestg1.zipcodes where not exists(select zip_cd from updatearea.zipcodes_new where zip_cd is not null);** result returns 0 rowcountcould 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

j355ga 100 posts Joined 12/05
26 Jun 2008

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 asselect * from foo a where not exists (select * from bar b where a.col = b.col)

Jeff

You must sign in to leave a comment.