If it's working fine, why do you want to change it?
This is one of those cases where leaving alone is better, as the LEFT JOIN version of this batch is about 4% slower than the NOT IN you are currently using.
I set up some
#Temp tables for this to test these two head-2-head, and reviewed the Actual Execution Plans to find that the comparison is 49/51 in favor of the first statement
SELECT StateID, [Name]
FROM #tblState
WHERE StateID NOT IN ( SELECT c.StateID FROM #tblCity c )
SELECT s.StateID, s.[Name]
FROM #tblState s
LEFT JOIN #tblCity c ON s.StateID = c.StateID
WHERE c.StateID IS NULL
I did also test utilizing the EXCEPT operator, and found this to be about 50% slower than either the JOIN or IN variants above it (split was 28/29/43)
SELECT s.StateID FROM #tblState s
EXCEPT
SELECT c.StateID FROM #tblCity c
Please note that this will only return the StateID and not the actual StateName. So this would either need to be in an IN statement or JOINed. Either will further reduce performance. Added onto the previous 3 queries the Execution Plan splits become 17-17-26-41
SELECT StateID, [Name]
FROM #tblState
WHERE StateID IN (
SELECT s.StateID FROM #tblState s
EXCEPT
SELECT c.StateID FROM #tblCity c
)
Now these are on tables without Primary Keys. I did add them in as well as an Index on the StateID column in the #City table. This sped things up considerable. JOIN vs IN vs EXCEPT changes the Actual Execution Plans to be virtually identical: 33/34/33.
With an IN added onto the EXCEPT query the split is 22/22/22/34.