在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:

假设我有一个值列表，例如1,2,3,4,5和一个表，其中某些列中存在某些值。这是一个例子：

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:

我想创建一个SELECT语句，它将包含列表中的所有值以及与这些值匹配的那些行的信息，即在我的列表和表之间执行LEFT OUTER JOIN，因此结果就像如下：

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?

如何在不创建临时表或使用多个UNION运算符的情况下执行此操作？

5 个解决方案

#1

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

如果在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

Don't know if Oracle has similar construction

不知道Oracle是否有类似的结构

#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).

从这个来源采用以下oracle解决方案。基本思想是利用oracle的分层查询。您必须指定列表的最大长度（下面的示例查询中为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 )
        ;

check out this sql fiddle to see it work.

看看这个sql小提琴看它是否有效。

#3

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

稍晚一点，但对于Oracle，你可以做这样的事情来得到一个值表：

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

#4

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

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

Caution: This is SQL Server implementation.

警告：这是SQL Server实现。

fiddle

小提琴

#5

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:

假设您的值为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

#1