SELECT * FROM `TABLE` WHERE
(`PRIMARY_KEY`= `VALUE1`) OR
(`PRIMARY_KEY`= `VALUE2`) OR
(`PRIMARY_KEY`= `VALUE3`) OR
(`PRIMARY_KEY`= `VALUE4`) OR
(`PRIMARY_KEY`= `VALUE5`) OR ...
This works. But is there a faster way?
views:
61answers:
4
+1
A:
SELECT * FROM table WHERE primary_key IN (value1, value2, ...)
Ignacio Vazquez-Abrams
2010-03-11 17:41:15
A:
You can use the IN keyword to achieve this:
SELECT * FROM Table WHERE PRIMARY_KEY IN (VALUE1, VALUE2, ...)
Justin Ethier
2010-03-11 17:41:30
+4
A:
Using the value in (list) construct is not faster, but the SQL-code will be much easier to read/understand once someone needs to maintain the code.
SELECT *
FROM `TABLE`
WHERE `PRIMARY_KEY` in( `VALUE1`
, `VALUE2`
, `VALUE3`
, `VALUE4`
, `VALUE5`
)
Updated: Rewritten to reflect the feedback from the comments.
lexu
2010-03-11 17:41:31
I think it's a common fallacy that `in()` is faster that chained `OR` statements (or vice-versa). It's just simply less typing. I tried to find some references to this, but getting a useful Google result where "in" and "or" are vital keywords is difficult =P
Peter Bailey
2010-03-11 17:55:16
@Peter Bailey: You are probably right if/when we are talking about DB performance, and I was debating with myself if I should should use "will or might in the answer above ... , **BUT** consider programmer and DBA performance (in head scratching hours) when maintaining / optimizing that code .. On the other hand, it will be much easier for the optimizer (human or MySQL) to relize that an index should be used (moot in this case, PRIMARY_KEY is PK)
lexu
2010-03-11 18:30:51
@Peter Bailey: That was my first thought when I read the answers too, so I tried it with Oracle 10g, and **execution plan is the same**. I assume that other DBs are going to handle it the same way. @lexu: It is certainly easier for humans to read, and I would always do it that way. Non-human optimizers should be fine with both ways, though :)
Peter Lang
2010-03-11 18:37:04
lexu
2010-03-11 18:46:56
A:
what about
SELECT * FROM `TABLE` WHERE
(`PRIMARY_KEY`IN( `VALUE1`,`VALUE2`,`VALUE3`,`VALUE4`,`VALUE5`) )
SQLMenace
2010-03-11 17:42:13