2

There is N no. of strings.

let's say 'foo', 'bar', 'chi', 'xyz', 'moo'.

I want to form a dummy table, say X for joining with another table, say Y which has those strings in one of the column i.e name in . Trying to pull out data by running the below query.

select Y.name, Y.age from(**select ('foo', 'bar', 'chi', 'xyz', 'moo') as name**) X left join Y on X.name = Y.name;

I know that text within ** is not the proper SQL syntax, but looking for something similar to have to run query in Oracle SQL.

Any suggestion or ideas most welcome.

Sriram M
  • 482
  • 3
  • 12

3 Answers3

6

Use a VARRAY or collection and join on the COLUMN_VALUE pseudocolumn:

SELECT y.*
FROM   TABLE( SYS.ODCIVARCHAR2LIST( 'foo', 'bar', 'chi', 'xyz', 'moo' ) ) t
       INNER JOIN -- or LEFT OUTER JOIN
       y
       ON t.COLUMN_VALUE = y.name;

SYS.ODCIVARCHAR2LIST is one of several pre-existing VARRAYs but you can easily create your own collection:

CREATE TYPE Char3List IS TABLE OF CHAR(3)
/

SELECT y.*
FROM   TABLE( Char3List( 'foo', 'bar', 'chi', 'xyz', 'moo' ) ) t
       INNER JOIN -- or LEFT OUTER JOIN
       y
       ON t.COLUMN_VALUE = y.name;

or, with a collection (but not a VARRAY like SYS.ODCIVARCHAR2LIST), you don't even need to use a join:

SELECT *
FROM   y
WHERE  name MEMBER OF Char3List( 'foo', 'bar', 'chi', 'xyz', 'moo' );

You can even pass it in to a bind parameter as an array from an external language.

MT0
  • 143,790
  • 11
  • 59
  • 117
2

One simple method is union all:

select names.name, Y.age
from (select 'foo' as name from dual union all
      select 'bar' as name from dual union all
      select 'chi' as name from dual union all
      select 'xyz' as name from dual union all
      select 'moo' as name from dual
     ) names left join
     Y
     on names.name = Y.name;

Note that the select has changed to take the name from the first table, not the second (after all, there may be no match).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, but in my case, the string list is huge, nearly 10K values, is there any better approach rather than union all? or any inbuilt functions like analytic function in Oracle? – Sriram M Oct 10 '17 at 19:11
0
with names as (
      select 'foo' as name from dual 
      union all
      select 'bar'  from dual 
      union all
      select 'chi' from dual 
      union all
      select 'xyz' from dual 
      union all
      select 'moo' from dual
     ) 
     select x.name, y.age
from  names x 
left join y
on x.name = y.name;
Buddhi
  • 416
  • 4
  • 14
  • Thanks, apart from union all with dual table, is there any simple approach for this. because, list of string need to be used is massive. It will be really a painful thing to form a such huge query (dummy table dual) with all those values. – Sriram M Oct 10 '17 at 19:23
  • You can for queries using excel or you can use External Tables and read from flat file. – Buddhi Oct 10 '17 at 19:37