Use the NOT EXISTS predicate instead of NOT IN if the following conditions are true:
- Some column of the NOT IN condition is defined as nullable.
- Any rows from the main query with a null in any column of the NOT IN condition should always be returned.
- Any nulls returned in the select list of the subquery should not prevent any rows from the main query from being returned.
For example, if all of the previous conditions are true for the following query, use NOT EXISTS instead of NOT IN:
SELECT dept, DeptName FROM Department WHERE Dept NOT IN (SELECT Dept FROM Course);
The NOT EXISTS version looks like this:
SELECT dept, DeptName FROM Department WHERE NOT EXISTS (SELECT Dept FROM Course WHERE Course.Dept=Department.Dept);
That is, either Course.Dept or Department.Dept is nullable and a row from Department with a null for Dept should be returned and a null in Course.Dept should not prevent rows from Department from being returned.