CODEWITHSUNDEEP
sqlsql-serveroracle-database
uncoder
uncoder

Reputation: 1886

Joining a list of values with table rows in SQL

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?

Upvotes: 57

Views: 106158

Answers (6)

Charles Bretana
Charles Bretana

Reputation: 146557

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;

Upvotes: 98

Amarnadh
Amarnadh

Reputation: 11

For getting sequential numbers as required for part of output (This method eliminates values to type for n numbers):

declare @site as int
set @site = 1
while @site<=200
begin
insert into ##table
values (@site)

set @site=@site+1
end

Final output[post above step]: 

select * from ##table
select v.id,m.name from  ##table  as v
left outer join [source_table] m
 on m.id=v.id

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3596

Bit late on this, but for Oracle you could do something like this to get a table of values:

SELECT rownum + 5 /*start*/ - 1 as myval
FROM dual
CONNECT BY LEVEL <= 100 /*end*/ - 5 /*start*/ + 1

... And then join that to your table:

SELECT *
FROM
(SELECT rownum + 1 /*start*/ - 1 myval
FROM dual
CONNECT BY LEVEL <= 5 /*end*/ - 1 /*start*/ + 1) mypseudotable
left outer join myothertable
    on mypseudotable.myval = myothertable.correspondingval

Upvotes: 2

user353gre3
user353gre3

Reputation: 2755

Assuming myTable is the name of your table, following code should work.

;with x as 
(
  select top (select max(id) from [myTable]) number from [master]..spt_values
),
y as
(select row_number() over (order by x.number) as id
from x)
select y.id,  t.name
from y left join myTable as t
on y.id = t.id;

Caution: This is SQL Server implementation.

fiddle

Upvotes: 1

xbb
xbb

Reputation: 2163

Suppose your table that has values 1,2,3,4,5 is named list_of_values, and suppose the table that contain some values but has the name column as some_values, you can do:

SELECT B.id,A.name
FROM [list_of_values] AS B
LEFT JOIN [some_values] AS A
ON B.ID = A.ID

Upvotes: -1

collapsar
collapsar

Reputation: 17248

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.

Upvotes: 4

Related Questions