-
1. Re: Difference Between IN and OR
Paulzip Aug 19, 2018 12:17 PM (in response to SQL_Users)1 person found this helpfulThey 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.
-
2. Re: Difference Between IN and OR
Frank Kulash Aug 19, 2018 10:16 PM (in response to SQL_Users)1 person found this helpfulHi,
Chacks wrote:
Hello,
What is the difference between the following two SQL statements,
- SELECTCOUNT(*)
- FROMproducts
- WHEREt2IN('?','P?','P??')
and
- SELECTCOUNT(*)
- FROMproducts
- WHERE(t2='?'ORt2='P?'ORt2='P??')
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!
-
3. Re: Difference Between IN and OR
rp0428 Aug 19, 2018 5:32 PM (in response to SQL_Users)What is the difference between the following two SQL statements,
What did you find out when you tested both of them?
Did they give you the same results?
Did they produce the same execution plan?
Did they take the same amount of time?
The BEST, and often fastest, way to get an answer to a 'what happens if ..' question is: TRY IT AND SEE!
-
4. Re: Difference Between IN and OR
John_K Aug 20, 2018 8:08 AM (in response to SQL_Users)1 person found this helpfulIf 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).
-