SQL performance error for IN and OR

Question

Imagine that there is sql and in some part of this SP there is a WHERE clause that searches for two identifiers;

... WHERE ID IN (123,1245)

instead of using IN if I use OR;

so that I win or lose. OR are both equal?

Answer 1

Oracle, SQL Serverand MySQLwill generate the same plans for both queries.

If the field of interest is not indexed, this is a simple filter.

If the field is indexed and index search is selected by the optimizer:

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

PostgreSQLgenerates nominally different plans: ORwith two index conditions in the first case and one index condition ANY(123, 1245):INTEGER[]in the second case.

, , .

, , .

:

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

( ) , ( ) .

Answer 2

, ( EXISTS LEFT JOIN). , .

, , , . EXPLAIN PLAN, .

Answer 3

: , IN (...,..) OR SQL.

, .

Answer 4

... ( , ID - PK) .

, SQL , - SSMS "", " ". . , SQL Server . .

Answer 5

​​ IN OR, , .

Answer 6

(Oracle, SQL Server, MySQL) - IN OR. , .

Answer 7

. RDBMS IN OR ( ), , .

MySQL, , , IN

Answer 8

Most enterprise-level DBMSs have the ability to analyze the query execution plan. You should use this function to compare plans for two options. This is the best way to find out what is going on.