SELECT from table with Varying IN list in WHERE clause

Your requirement is called as Varying IN-lists. See Varying IN list of values in WHERE clause

Reason : IN ('1, 2, 3') is NOT same as IN (1, 2, 3) OR IN('1', '2', '3')

Hence,

SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);

is same as

SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');

which would thrown an error ORA-01722: invalid number -

SQL> SELECT * FROM temp_id WHERE data_id IN('1, 2, 3');SELECT * FROM temp_id WHERE data_id IN('1, 2, 3')                                       *ERROR at line 1:ORA-01722: invalid numberSQL> SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp);SELECT * FROM temp_id WHERE data_id IN(SELECT ids FROM temp)                                              *ERROR at line 1:ORA-01722: invalid number

NOT same as

SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);

which would give you correct output -

SQL> SELECT * FROM temp_id WHERE data_id IN(1, 2, 3);   DATA_ID----------         1         2         3

Solution :

For your requirement, you can achieve it like this -

SQL> SELECT * FROM temp;IDS--------------------------------------------------------------1, 2, 3SQL> SELECT * FROM temp_id;   DATA_ID----------         1         2         3         4         5SQL> WITH data AS  2    (SELECT to_number(trim(regexp_substr(ids, '[^,]+', 1, LEVEL))) ids  3    FROM temp  4      CONNECT BY instr(ids, ',', 1, LEVEL - 1) > 0  5    )  6  SELECT * FROM temp_id WHERE data_id IN  7    (SELECT ids FROM data  8    )  9  /   DATA_ID----------         1         2         3

Alternatively, you can create your own TABLE function or a Pipelined function to achieve this. Your goal should be to split the comma-separated IN list into multiple rows. How you do it is up to you!

Working demo

Let's take an example of the standard EMP table in SCOTT schema.

I have a list of jobs in a string, and I want to count the employees for those jobs:

SQL> SET serveroutput ONSQL> DECLARE  2    str VARCHAR2(100);  3    cnt NUMBER;  4  BEGIN  5    str := q'[CLERK,SALESMAN,ANALYST]';  6    SELECT COUNT(*) INTO cnt FROM emp WHERE JOB IN (str);  7    dbms_output.put_line('The total count is '||cnt);  8  END;  9  /The total count is 0PL/SQL procedure successfully completed.

Oh! What happened? The standard emp table should give an output 10. The reason is that the varying IN list.

Let's see the correct way:

SQL> SET serveroutput ONSQL> DECLARE  2    str VARCHAR2(100);  3    cnt NUMBER;  4  BEGIN  5    str := q'[CLERK,SALESMAN,ANALYST]';  6    SELECT COUNT(*)  7    INTO cnt  8    FROM emp  9    WHERE job IN 10      (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) 11      FROM dual 12        CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 13      ); 14    dbms_output.put_line('The total count is '||cnt); 15  END; 16  /The total count is 10PL/SQL procedure successfully completed.

There is another way in which this might be accomplished, namely by using LIKE:

SELECT ti.*  FROM temp t, temp_id ti WHERE ',' || REPLACE(t.ids, ' ') || ',' LIKE '%,' || TO_CHAR(ti.data_id) || ',%'

(I used REPLACE() above to get rid of the extraneous whitespace in the ID list - makes things a bit simpler.) Alternately, one could use REGEXP_LIKE():

SELECT ti.*  FROM temp t, temp_id ti WHERE REGEXP_LIKE(REPLACE(t.ids, ' '), '(^|,)' || TO_CHAR(ti.data_id) || '(,|$)')

[The caret (^) and dollar-sign ($) characters are to match the start and end, respectively, of the string -- so the ID can either match something at the start of the string (ending with a comma or the end of the string) or something starting with a comma (again, ending with a comma or the end of the string).]