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

    Difference Between IN and OR

    SQL_Users

      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

          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
          • 2. Re: Difference Between IN and OR
            Frank Kulash

            Hi,

            Chacks wrote:

             

            Hello,

             

            What is the difference between the following two SQL statements,

             

            1. SELECTCOUNT(*)
            2. FROMproducts
            3. WHEREt2IN('?','P?','P??')

             

            and

             

            1. SELECTCOUNT(*)
            2. FROMproducts
            3. 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
            • 3. Re: Difference Between IN and OR

              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

                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

                  Thanks Paulzip Frank Kulash rp0428 John_K

                   

                  Much appreciated for the insight.