SQL performance issue for IN and OR

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?

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.

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.

Back to my years at school: I was taught that the IN (...,..) expression was decomposed in a pack of OR statements before execution of the SQL.

It seams it is just a more comprehensive code.

Those are logically the same...both (assuming your ID column is the PK) will perform an index seek.

An easy way to understand how SQL treats queries like this is to click the button in the toolbar in SSMS to the right of the 'Execute' button called 'Display Estimated Execution Plan'. There is also an option to show the actual plan when you execute. Either way this will let you see how SQL Server breaks down the query into smaller parts. This is the most important tool in tuning a query.

The critical point in this case is not the use of IN or OR, but that you have the ID field appropriately indexed.

They are identical in almost all newer versions of database systems (Oracle, SQL Server, MySQL) - the optimizer converts the IN to a series of ORs. You can view the execution plan in your particular version to see it.

Depends on the implementation. In most RDBMS's IN and OR statements are handled identically (as they are logically identical), but a few contain optimizations that handle it more optimally.

MySQL for instance does contain optimizations where it is usually able to execute IN clauses as a range scan

Most enterprise level RDBMS have the ability to analyze to the execution plan of the query. You should use this feature to compare the plans of the two variations. That is the best way to know for sure what is going on.