tsql (not in subquery) help on syntax

[ecka00]

I run the following with the "IN" clause and receive all but 2 rows that don't exist in both tables. I try running the same query with "NOT IN" to identify the 2 rows that don't match and instead receive a nullset. Please help!


select a.employeeIdNum

from phonebook.lawsonextract a
where a.employeeidNum is not null

and a.employeeidNum NOT IN (select b.employeeIdNum from phonebook.employeelist b)

[MAK]

select a.employeeIdNum from (select employeeIdNum from phonebook.lawsonextract where employeeIdNum is not NULL) a where a.employeeidNum NOT IN (select b.employeeIdNum from phonebook.employeelist b)

[ecka00]

Nope---sorry same resultset

[nosepicker]

The pattern is full

You probably have some NULL values for the "employeeIdNum" column in the "employeelist" table. If you use an "IN" comparison against NULL values, SQL Server will by default evaluate that as an unknown, or a NULL. Therefore, its better if you use the EXISTS or NOT EXISTS comparison:

select a.employeeIdNum
from phonebook.lawsonextract a
where a.employeeidNum is not null
and NOT EXISTS (select 1 from phonebook.employeelist b
where a.employeeidNum = b.employeeIdNum)

[ecka00]

that worked---thanks to both of you for the help. -allen