All Forums Database
matty.jiang 4 posts Joined 03/06
24 Mar 2006
some problems about TD KEYWORD 'NOT IN'

image one col named 'Num' contains value domain (null, 1 ,2 , 3)if we writesel *from table_namewhere Num not in (2,3)we just can get answer set containing rows with (Num=1), losing rows having null valueIt's really a problem.

24 Mar 2006

Matty,I dont think it is problem with the NOT IN clause.NOT IN is nothing but where value NOT EQUAL sel * from tablewhere num NOT IN (2,3)will take each value and see it is not equal to 2 and not equal to 3for 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 answersetfor 2it 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 answersetin case of NULLNULL not equal to 2 will result in INVALID or NOT KNOWN and NULL not equal to 3 is INVALIDso the result INVALID and INVALID will result in INVALID or something that is not TRUE so this row will not be in 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.

Barry-1604 176 posts Joined 07/05
24 Mar 2006

If you want rows that have NULL in the Num column, you could also use COALESCE to do this:sel *from table_namewhere COALESCE(Num,-99999) not in (2,3)You just have to pick a value that you're not looking for in your "NOT IN" clause.Hope this helps.Barry

You must sign in to leave a comment.