SQL performance issue for IN and OR

Question

imagine there is an sql and in some part of that SP, there is WHERE clause which looks for two IDs;

... WHERE ID IN (123,1245)

instead of using IN , if I use OR;

what would I gain or loose. OR both of them is equal?

Answer 1

There's an excellent article discussing a similar issue (NOT IN vs. EXISTS vs. LEFT JOIN). Basically, it shows that they are very similar and that the different optimizers handle these issues very well.

I would expect that the optimizer handles the two alternatives you mention lead to similar execution plan. Check with EXPLAIN PLAN if there are differences.

Answer 2

Oracle, SQL Server and MySQL will generate the same plans for both queries.

If the field in question is not indexed, this is just a plain filter.

If the field is indexed and the index seek is chosen by the optimizer:

• Oracle uses INLIST ITERATOR
• SQL Server uses CONSTANT SCAN
• MySQL uses range access method

PostgreSQL generates nominally different plans: OR with two index conditions in the first case and a single index condition ANY(123, 1245):INTEGER[] in the second case.

This, however, has no difference in practice as well.

Note that both these queries will always use the same access method for both values, while it may be more efficient to use different access methods.

Some queries may benefit from rewriting them as a union:

SELECT  *
FROM    mytable
WHERE   id = 123
        AND …
UNION ALL
SELECT  *
FROM    mytable
WHERE   id = 1245
        AND …

Depending on the fields selectivity, this may (or may not) generate different execution plans for two queries, which may (or may not) gain efficiency.