Joining a list of values with table rows in SQL

Question

Suppose I have a list of values, such as 1, 2, 3, 4, 5 and a table where some of those values exist in some column. Here is an example:

id  name  1  Alice  3  Cindy  5  Elmore  6  Felix 

I want to create a SELECT statement that will include all of the values from my list as well as the information from those rows that match the values, i.e., perform a LEFT OUTER JOIN between my list and the table, so the result would be like follows:

id  name  1  Alice  2  (null)  3  Cindy  4  (null)  5  Elmore 

How do I do that without creating a temp table or using multiple UNION operators?

Answer 1

If in Microsoft SQL Server 2008 or later, then you can use Table Value Constructor

 Select v.valueId, m.name   From (values (1), (2), (3), (4), (5)) v(valueId)      left Join otherTable m         on m.id = v.valueId 

Postgres also has this construction VALUES Lists:

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter) 

Also note the possible Common Table Expression syntax which can be handy to make joins:

WITH my_values(num, str) AS (     VALUES (1, 'one'), (2, 'two'), (3, 'three') ) SELECT num, txt FROM my_values 

With Oracle it's possible, though heavier From ASK TOM:

with id_list as (   select 10 id from dual union all   select 20 id from dual union all   select 25 id from dual union all   select 70 id from dual union all   select 90 id from dual )   select * from id_list; 

Answer 2

the following solution for oracle is adopted from this source. the basic idea is to exploit oracle's hierarchical queries. you have to specify a maximum length of the list (100 in the sample query below).

   select d.lstid         , t.name      from (                select substr(                            csv                          , instr(csv,',',1,lev) + 1                          , instr(csv,',',1,lev+1 )-instr(csv,',',1,lev)-1                       )  lstid                  from (select ','||'1,2,3,4,5'||',' csv from dual)                     , (select level lev from dual connect by level <= 100)                 where lev <= length(csv)-length(replace(csv,','))-1                    ) d left join test  t on ( d.lstid = t.id )         ; 

check out this sql fiddle to see it work.