6 Replies Latest reply: Mar 20, 2014 6:08 AM by 2618432 RSS

    in clause with subquery in oracle


      Hi Friends,

      I have two tables say table1 and table2 with below details

      create table test1(id number, name varchar2(20));
      insert into test1 values(11,'micro');
      insert into test1 values(22,'soft');

      create table test2(id number, name varchar2(20));
      insert into test2 values(77,'micro,soft');

      1)if i use below query i am getting no rows selectd
      select * from test1 t1 where t1.name in ( select ''''||replace(t2.name,',',''',''')||''''  from test2;

      2)if i fire subquery alone output i am getting is : 'micro','soft'
      select ''''||replace(t2.name,',',''',''')||''''  from test2;

      but if i fire query(1) i need the result
      id   name
      11 micro
      22 soft

      can some one please help me to get the same result with query (1).

        • 1. Re: in clause with subquery in oracle

          Your Query does throws error.

          May be you have missed some text from your query.


          select  *  from  test1;

          ID   NAME

          ----   --------

          11    micro

          22    soft


          SELECT ''''||listagg(name,',') within group(order by null)||''''  NAME FROM test1  ;





          • 2. Re: in clause with subquery in oracle

            Few things


            1. Your table TEST2 is not in first normal form (1NF). That's ugly!!

            2. IN clause does not evaluate a single variable or column with delimited values as multiple values they are just single value.


            FAQ has some details, read Re: 7. List of values in an IN clause?


            And this is how its done (one way)


            SQL> select id, name
              2    from test1
              3   where name in (
              4                   select regexp_substr(name, '[^,]+', 1, level)
              5                     from test2
              6                    where id = 77
              7                  connect
              8                       by level <= length(name)-length(replace(name, ','))+1
              9                 );


                    ID NAME
            ---------- --------------------
                    11 micro
                    22 soft

            • 3. Re: in clause with subquery in oracle

              Here you go


              SELECT *

              FROM test1 WHERE NAME IN (

                  SELECT REGEXP_SUBSTR (NAME,'[^, ]+',1,LEVEL) value

                      FROM (SELECT NAME FROM test2)

                                 connect BY REGEXP_SUBSTR (NAME,'[^, ]+',1,LEVEL) IS NOT NULL)

              • 4. Re: in clause with subquery in oracle

                Use the string splitting technique:


                SQL> select regexp_substr(name, '[^,]+', 1, rn)

                  2                  from   test2

                  3                  cross join ( select rownum rn

                  4                               from ( select max (length(regexp_replace(name, '[^,]+')))+1 mx

                  5                                       from   test2

                  6                                     )

                  7                               connect by level <= mx

                  8                             );







                2 rows selected.


                And then add the IN-query:


                SQL> select *

                  2  from   test1

                  3  where  name in (select regexp_substr(name, '[^,]+', 1, rn)

                  4                  from   test2

                  5                  cross join ( select rownum rn

                  6                               from ( select max (length(regexp_replace(name, '[^,]+')))+1 mx

                  7                                       from   test2

                  8                                     )

                  9                               connect by level <= mx

                10                             )

                11                 );


                        ID NAME

                ---------- --------------------

                        11 micro

                        22 soft


                2 rows selected.



                Notes on Oracle: Splitting a comma delimited string the RegExp way, Part Two

                • 5. Re: in clause with subquery in oracle




                  you_data as

                  (select 77 id ,'micro,soft,,,' str from dual




                      regexp_substr(y.str,'[^,]+',1,level) elements

                  from you_data y

                  connect by regexp_substr(y.str,'[^,]+',1,level) is not null ;





                          ID  STR               ELEMENTS

                  ----------  -------------         ----------------------------------------------------

                          77 micro,soft,,,    micro

                          77 micro,soft,,,    soft



                  • 6. Re: in clause with subquery in oracle

                    Hi Friends,


                    Thanks you all for your quick responces. my prob has been solved with your valuable steps