5 Replies Latest reply on Aug 21, 2018 5:18 AM by SQL_Users

Difference Between IN and OR

SQL_Users Aug 19, 2018 11:37 AM

Hello,

What is the difference between the following two SQL statements,

SELECT COUNT (*)
  FROM products
 WHERE t2 IN ('?', 'P?', 'P??')

and

SELECT COUNT (*)
  FROM products
 WHERE (t2 = '?' OR t2 = 'P?' OR t2 = 'P??')

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

Appreciate any insight or help.

Table DDL and sample data

CREATE TABLE products
(
   t1   NUMBER,
   t2   VARCHAR2 (32)
);


SET DEFINE OFF;
Insert into PRODUCTS
   (T1, T2)
 Values
   (110, '?');
Insert into PRODUCTS
   (T1, T2)
 Values
   (110, 'P?');
Insert into PRODUCTS
   (T1, T2)
 Values
   (110, 'P??');
Insert into PRODUCTS
   (T1, T2)
 Values
   (220, '?');
Insert into PRODUCTS
   (T1, T2)
 Values
   (220, 'P?');
Insert into PRODUCTS
   (T1, T2)
 Values
   (220, 'P??');
COMMIT;

1. Re: Difference Between IN and OR

Paulzip Aug 19, 2018 12:17 PM (in response to SQL_Users)

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.
1 person found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Difference Between IN and OR

Frank Kulash Aug 19, 2018 10:16 PM (in response to SQL_Users)
Hi,
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!
1 person found this helpful
Like Show 1 Likes(1)

Actions
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!
Like Show 1 Likes(1)

Actions

4. Re: Difference Between IN and OR

John_K Aug 20, 2018 8:08 AM (in response to SQL_Users)

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).

1 person found this helpful

5. Re: Difference Between IN and OR

SQL_Users Aug 21, 2018 5:18 AM (in response to John_K)

Thanks Paulzip Frank Kulash rp0428 John_K

Much appreciated for the insight.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.