Which will be faster out of these two queries?

Question

SELECT * FROM table WHERE col IN (1,2,3)

or

SELECT * FROM table WHERE col = 1 OR col = 2 OR col = 3

Answer 1

They are the same.

Edit: They are the same in MySQL.

using an index:

mysql> explain select * from trees where id = 1 or id = 2;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | trees | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

mysql> explain select * from trees where id in (1,2);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | trees | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

using a full table scan:

mysql> explain select * from trees where version = 1 or version = 2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | trees | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

mysql> explain select * from trees where version in (1,2);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | trees | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Answer 2

It depends on the interpretation of the database vendor and how they represent the where statements.

Answer 3

In Microsoft SQL Server, I believe they are interpreted in exactly the same way.

Answer 4

likely or not, but true... Most databases convert the former syntax to the latter. Why wouldn't it? They are logically and functiuonally identical.

See here

Answer 5

With most RDBMSs the queries are processed through an optimizer before execution. And since your predicates are logically equivalent i would expect them to execute at the same speed because they will be optimized to execute in the same manner.

But it is db dependent. You didn't specify which engine you're using.

Answer 6

Run EXPLAIN SELECT ... on both.

Answer 7

In this case the queries are equivalents or the first one will be a little faster in a test. Certain optimizations are applicable to comparisons that use the IN operator from a subquery which is not the case here.

Answer 8

The correct answer is: depends on the engine. Use EXPLAIN to check what's going on.

Some databases will translate IN form to a series of ORed clauses. Others will not.

Postgres for example gives a lower (!) cost when using the IN form.

I'd personally go for a first form as (1) some engines may optimize it, (2) it's more intuitive, (3) is shorter.

Somewhat offtopic: please don't be tempted to overuse IN for joining the tables though. I've seen many times constructs like this:

SELECT * FROM employee WHERE boss_id IN (SELECT id FROM boss WHERE some_condition..)

I guess the reason for it is that somebody goes with the outer select, then finds out the condition in the referenced table by issuing the inner select, then just does copy & paste. Of course such construct is worse than the proper JOIN.