CODEWITHSUNDEEP
sqlconditional-statementswhere-clause
d2907
d2907

Reputation: 902

Differences between OR condition or IN condition in SQL

I wonder what is the difference in terms of performance or use of resources between a sentence in sql where I have a WHERE sentence of the kind 

WHERE VALUE IN (A,B,C .......)

or 

WHERE VALUE = A OR VALUE = B OR VALUE = C.....

I am wondering this because in an Oracle Query I have to search all the information related with this values. I can not create a temporary table. I just receive them from an external source. First I though I would be as simple as putting them inside the IN clause. The number of values is variable but never lower than 3000. On the other hand I read that there is a limit in Oracle for the quantity of values for kind of conditions, but until now I have never received more than 4000.

Upvotes: 1

Views: 794

Answers (2)

Bohemian
Bohemian

Reputation: 425013

They are logically the same (in that they produce the same result), but the optimizer will only use an index (if one exists) for one of the values from an OR - effectively not using an index (since O(log n) + O(n) = O(n)).

In my experience, when looking for ways to improve query performance, one of the first things I do is convert ORs on a column to a single IN (which usually has a great effect).

Upvotes: 0

Rahul
Rahul

Reputation: 77876

No difference at all. internally DB engine going to flatten that IN list to a ORed condition stack like

WHERE VALUE IN (A,B,C .......)

will get flatten to

WHERE VALUE = A OR VALUE = B ...

Upvotes: 1

Related Questions