(008) Daily SQL Learning: Oracle not Exists and not in use

Today encountered a problem, not in the query failed, I thought it was through, carefully checked the information, the original understanding is wrong!

Select  from temp_a a where between 1  and  -  and  not exists (Select*fromwhere a.value=b.value);

Then we can find out the results

Select  from temp_a a where between 1  and  -  and  not inch (Select from Temp_b);

The result is empty at this time.

After Google finally find out why: the inner table (temp_b) has a null value. The result set obtained with not in is empty. The following are the conclusions:

1, for not EXISTS query, the existence of NULL in the table has no effect on the query results; for a not in query, a null value in the inner table causes the final query result to be empty.

2, for the not exists query, the appearance of null values, the existence of a null value of the record will eventually output; for a not in query, the appearance of null values, the existence of a null value of the record will eventually be filtered, the other data is not affected.

This article transferred from: http://blog.csdn.net/haitaofeiyang/article/details/50158395

(008) Daily SQL Learning: Oracle not Exists and not in use