sqlperformanceoraclequery-performance

Is there any performance difference between IN and NOT IN Operators?

Is there any major query performance difference between IN and NOT IN operators in oracle database?

Solution

  • Like most performance questions, it depends.

    If there are no indexes then they should be roughly comparable.

    If you have an index on the limiting column then IN will likely be faster than NOT IN as IN can use an index seek while NOT IN might require a table scan.

    If there are very few distinct values of col1 and it's indexed, then NOT IN could end up using an index seek rather than a table scan.

    EXISTS or NOT EXISTS are typically faster.