splitting comma separated list into a temp table

Question

I want to convert the following IN query into inner join query :

select country, name, rank from table person_details 
   where country in ('india','USA','australia') 
   and   name in ('tom', 'jill', 'jack') 
   and   rank in ('first', 'third', 'fifith');

I have two questions:

1. this table is big so will changing this IN query into inner join will speed up things.

2. What will be best way to split this comma separated list to a column in some temp table. I have see many regex examples but they seem too complex and big.

I am using Oracle 11g DB.

Table snapshot :

Id   name   country   rank
 1    tom    india     first
 2    jill   USA      second
 3    jack   aus       first

Answer 1

select country, name, rank from table person_details

The query is syntactically incorrect. you don't need the keyword TABLE. Just do:

select country, name, rank from person_details

Strictly speaking, your table is not normalized. You should not store multiple values in a single column. Sooner or later you will see the performance issues. It is never too late to re-design your tables and store the values in separate columns.

Having said that, there are many ways to split comma delimited string into rows. here is one simple way using REGEXP_SUBSTR and INSTR in CONNECT BY clause:

SQL> WITH DATA AS(
  2  select q'['india','USA','australia']' countries,
  3  q'['tom', 'jill', 'jack']' names,
  4  q'['first', 'third', 'fifth']' ranks
  5  from dual
  6  )
  7  SELECT regexp_substr(translate(countries,'''',' '), '[^,]+', 1, LEVEL) countries,
  8  trim(regexp_substr(translate(names,'''',' '), '[^,]+', 1, LEVEL)) names,
  9  trim(regexp_substr(translate(ranks,'''',' '), '[^,]+', 1, LEVEL)) ranks
 10  FROM DATA
 11  CONNECT BY instr(countries, ',', 1, LEVEL - 1) > 0
 12  /

COUNTRIES                 NAMES                 RANKS
------------------------- --------------------- -------------------------
 india                    tom                   first
 USA                      jill                  third
 australia                jack                  fifth

SQL>

I have demonstrated other ways in my article ORACLE DELIMITED STRING MANIPULATION.