"where in" problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"where in" problem

Author

Topic

mirirom
Starting Member

7 Posts

Posted - 2009-07-27 : 12:01:19

hi,

there's probably an obvious answer to this, but for the life of me a group of us here at work can't seem to figure out this problem using a "where in" condition.

we've two tables containing a zip-code column. both columns are defined with the EXACT same datatype (int, nullable, non-computed, etc). we know that tblA has more distinct zip-codes than tblB (i.e. select distinct zipCode from tblA returns more rows than select distinct zipCode from tblB), so what's the problem with the following?


select distinct zipCode from tblA
where tblA.zipCode not in (select distinct zipCode from tblB)

running this returns 0 rows

however, this works just fine.


select distinct a1.zipCode from tblA a1
where a1.zipCode not in
   (select distinct a2.zipCode from tblA a2
    inner join tblB b2 on a2.zipCode = b2.zipCode)

any thoughts are greatly appreciated...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-27 : 12:25:55

tblB must contain a NULL zipCode.

http://www.simple-talk.com/sql/sql-training/sql-and-the-snare-of-three-valued-logic/