Объединение списка значений со строками таблицы в SQL

Предположим, что у меня есть список значений, таких как 1, 2, 3, 4, 5 и таблица, в которой некоторые из этих значений существуют в одном столбце. Вот пример:

id  name
 1  Alice
 3  Cindy
 5  Elmore
 6  Felix

Я хочу создать оператор SELECT, который будет включать все значения из моего списка, а также информацию из тех строк, которые соответствуют значениям, т.е. выполнить LEFT OUTER JOIN между моим списком и таблицей, поэтому результат будет выглядеть следующим образом:

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

Как это сделать, не создавая временную таблицу или используя несколько операторов UNION?

Ответы

Ответ 1

Если в Microsoft SQL Server 2008 или более поздней версии вы можете использовать Конструктор значений таблиц

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

Не знаю, имеет ли Oracle аналогичную конструкцию

Ответ 2

следующее решение для оракула принято из этого источника. основная идея - использовать иерархические запросы оракула. вы должны указать максимальную длину списка (100 в примере запроса ниже).

   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 )
        ;

просмотрите эту скрипту, чтобы увидеть, как она работает.

Ответ 3

Бит поздно, но для Oracle вы можете сделать что-то подобное, чтобы получить таблицу значений:

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

... И затем присоедините это к своей таблице:

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

Ответ 4

Предполагая, что myTable - это имя вашей таблицы, следующий код должен работать.

;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;

Внимание: это реализация SQL Server.

fiddle

Ответ 5

Предположим, что ваша таблица с значениями 1,2,3,4,5 имеет имя list_of_values и предположим, что таблица, содержащая некоторые значения, но имеющая столбец имен some_values, вы можете сделать:

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