Why not to use NOT IN?

NOT IN is handy syntax use. However, is it a syntax you can always use? Just see the following examples.

Let use say we have two tables named First and Second and data contains as follows.

Let’s say you execute following statement.

What do you think about about. You might think that you should get 2 – Oracle as the result. If so, you are WRONG!!!

Above query will result NOTHING!!! Yes nothing.

Let us try to replace NOT IN with NOT EXISTS and compare the results.

Well, NOT EXISTS returned the desired results.

Let us interchange the tables.

Again NOT IN not giving us the correct results. Point to note here is, NULL values not working very well with NOT IN. So make sure you are not dealing with NULL values when using NOT IN.