Re: IN vs. EXISTS ...... NOT IN vs. NOT EXISTS

On Mon, 09 Feb 1998 18:40:45 -0800, Kal Khatib <kal_at_gene.COM> wrote:

>I have statements where i use the above oracle functions.
>IN and EXISTS seem _not_ to be interchangable (same for negation)
>Could some explain the differnece in behavior between those functions
>(beside performace.. IN is slower I think)?.
>I find it tricky to use one vs. the other. I end up having to
>experimenting with permutation
>to get the logic right.
>thanks
>Kal.

Hi Kal,

you can transform the IN/NOT IN expression into an equivalent EXISTS/NOT EXISTS expression by using a correlated subquery. Consider this example:

SELECT col_1
  FROM table_1
 WHERE id_1 IN

      (SELECT id_2
         FROM table_2
        WHERE col_2 < some_value)

would transform to

SELECT t1.col_1
  FROM table_1 t1
 WHERE EXISTS

      (SELECT 1   -- any 'dummy' expression will do here
         FROM table_2 t2
        WHERE t1.id_1 = t2.id_2   -- the correlation expression
          AND t2.col_2 < some_value)

If the result sets retrieved by both the outer and the inner query in the first example is small enough (based on the criteria and selectivity of available indexes) that you would favour an index access over a full table scan, the equivalent second variation usually gives better performance. The subquery will stop on the first hit, the execution plan will usually be a nested loop, and if you have indexes on the columns of your correlation expression, only index reads will occur. BTW, if you are operating on large quantities of data, you should definitely _not_ use the NOT IN operation, it is a real performance killer.

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net