Matty, I dont think it is problem with the NOT IN clause.
NOT IN is nothing but where value NOT EQUAL TO. so sel * from table where num NOT IN (2,3)
will take each value and see it is not equal to 2 and not equal to 3
for the value 1 it is not equal to 2 and it is not equal to 3 so TRUE and TRUE will result in TRUE and this row or num 1 is retrieved in the answerset
for 2 it goes thru same steps and we get FALSE (2 not equal to 2) and TRUE (2 not equal to 3) false and true result in false and this row or num 2 is not retreived in answerset
in case of NULL NULL not equal to 2 will result in INVALID or NOT KNOWN and NULL not equal to 3 is INVALID so the result INVALID and INVALID will result in INVALID or something that is not TRUE so this row will not be in answerset.
so if you want to get the NULL add in where clause.
You should be very carefull when you do some left or right outer joins and you are using columns that have NULL values. You will get some funny results if you forget the NULLs.