It may be because of NULL in PositionStatus column. Try the following:SELECT *FROM PersonWHERE DepartureDate IS NULL AND Person_ID NOT IN (SELECT pos_person_ID FROM Position WHERE ISNULL(PositionStatus,'') = 'Encumbered' )
If that works, you might try this, which I like better (but probably will generate the same query plan)SELECT *FROM Person p1WHERE DepartureDate IS NULLAND NOT EXISTS ( SELECT * FROM Position p2 WHERE p2.pos_person_ID = p1.person_id AND p2.PositionStatus = 'Encumbered')