SQL pitfall ('NOT IN')

It was frustrating recently to see that the following simple SQLs behave fundamentally different though one result was supposed to be the negative of the other.

-- Lists emloyees present in the tmp table.
select * from empl empl
where empl.empl_id in (select empl_id from tmpTable);

-- Expected to list the employees that are not present in the tmp table
-- Very similar to the above query, but using 'not in' construct.
select * from empl empl
where empl.empl_id not in (select empl_id from tmpTable);

No results were returned from this query though a number of records were expected to be returned.

After digging into the basics and spending quite a bit of time googling, a record with null emloyee id was found in the tmpTable, which originated from the CSV source file used to populate this table.

So I put in a filter 'where empl_id is not null' for second query (based on 'not in') alone. Realized that I should have used 'not exists', and would have avoided this pitfall.