1

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.

d2907
  • 798
  • 3
  • 15
  • 45
  • 1
    No difference at all – Giorgi Nakeuri May 26 '15 at 21:52
  • I suppose this will depend on the type of database software you use. If this is really relevant for you, you should go and measure this with an EXPLAIN statement or alike. Then you will see if the parser does a good job and handles both the same. And: IN has the clear advantage that you could use sub select instead of static values or a variable number of values. – Marged May 26 '15 at 21:55
  • What RDBMS are you using? – Martin Smith May 26 '15 at 21:55
  • 1
    There's a great discussion of this, including real MySQL performance data at http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause This has also been discussed for Oracle at http://stackoverflow.com/questions/6514906/in-vs-or-of-oracle-which-faster MS SQL Server has been discussed at http://stackoverflow.com/questions/2110715/sql-server-select-in-vs-or – Rob Fagen May 26 '15 at 22:04
  • @rob regarding the question in your deleted answer once you get 3k rep you can vote to close as dupe. In absence of info about the RDBMS it is not clear which of those links would be the best target though! – Martin Smith May 26 '15 at 22:05

2 Answers2

1

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 ...
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

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).

Bohemian
  • 412,405
  • 93
  • 575
  • 722