Monday, January 23, 2012

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.

image

Let’s say you execute following statement.

image

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.

image

Well, NOT EXISTS returned the desired results.

Let us interchange the tables.

image

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.

No comments:

Post a Comment