1

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
user2696466
  • 650
  • 1
  • 14
  • 33

1 Answers1

0

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • hi lalit, columns do not have multiple values. CSV are the filter selection criteria by a user. if this is what you meant. – user2696466 Mar 10 '15 at 06:30
  • yeah thanks it does. but do we have to do all this operation for converting CSV to a temp table column. If yes then please could you just give a brief of as how this query is working. I could not figure this out. MSSQL had simpler ways of doing this. – user2696466 Mar 10 '15 at 06:40
  • It is a simple method. All i am doing is, using `regexp_substr` extracting each substring between the commas, and then the connect by clause will convert it into rows. The LEVEL indicates the row increment. There are 3 values, so level will be 3 in this case. Please mark it answered so that it helps others to find this as a correct solution. – Lalit Kumar B Mar 10 '15 at 07:00
  • one one thing more lalit. this CSV to table will give me a temp table and now i want to use this result to do inner join with another table how can i do this. i am pasting queries i have tried. – user2696466 Mar 10 '15 at 07:05
  • one one thing more lalit. this CSV to table will give me a temp table and now i want to use this result to do inner join with another table how can i do this. i am pasting queries i have tried. select distinct account_id , account from accounts inner join (WITH DATA AS( select q'['India','USA']' countries from dual) SELECT regexp_substr(translate(countries,'''',' '), '[^,]+', 1, LEVEL) countries FROM DATA CONNECT BY instr(countries, ',', 1, LEVEL - 1) > 0 ) Temp on accounts.BILLING_COUNTRY = Temp.countries; – user2696466 Mar 10 '15 at 07:05
  • That now how you join with the WITH clause. You need to simply consider the WITH as a table. So, do it as `WITH DATA AS(...) SELECT column_list FROM DATA INNER JOIN ACCOUNTS...` – Lalit Kumar B Mar 10 '15 at 07:18