Difference Between IN and OR

They are functionally equivalent. The former gets rewritten as the latter during processing by the optimiser.  "IN" just allows your queries to be more succinct and maintainable. It allows new predicates to be added quickly.

Note, INs of that form limits to 1000 items, ORs don't have such a limitation.

Hi,

Chacks wrote:

 

Hello,

 

What is the difference between the following two SQL statements,

 

 

and

 

 

 

Does the above two can be used interchangeably? or does the above two have pros and cons?

 

Appreciate any insight or help.

...

As Paulzip said, both ways produce the same results, and are equally efficient.

Most people find IN more convenient, because it's easier to write and read, especially if there are other conditions, connected by AND, e.g.

WHERE   t1  > 100

AND     t2  IN ('?', 'P?', 'P??')

It's also easier to rewrite IN to use a sub-query.

Thanks for posting the sample data!

If you do an explain plan, you can see how they are related:

select * from fnd_user where user_name='OPERATIONS' or user_name='SYSADMIN' or user_name='AUTOINSTALL';

Plan hash value: 2679127239

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |   984 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FND_USER    |     3 |   984 |     5   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | FND_USER_U2 |     3 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("USER_NAME"='AUTOINSTALL' OR "USER_NAME"='OPERATIONS' OR 
              "USER_NAME"='SYSADMIN')

select * from fnd_user where user_name in ('OPERATIONS','SYSADMIN','AUTOINSTALL');

Plan hash value: 2679127239

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |   984 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FND_USER    |     3 |   984 |     5   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | FND_USER_U2 |     3 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("USER_NAME"='AUTOINSTALL' OR "USER_NAME"='OPERATIONS' OR 
              "USER_NAME"='SYSADMIN')

See both plans have the same hash value, both use an "INLIST ITERATOR" operation, and the access path for both shows that the "IN" is done as an "OR" (as already explained).