我有一个名为test\u table\u 1的表和一个名为temp\u test\u view\u 1的视图。
目标是为测试表1中的每个id找到新的\u类别\u id。新类别id和id之间的关系在视图temp\u test\u view\u 1中
为此，我使用左联接，基表是test\ table\ 1。
在我看来，左连接返回了一些意想不到的结果。
以下是表test\u表1中包含的所有数据：

select * from test_table_1: 

id   name
1    'a'
null 'd'
3    'd'
2    'c'
2    'b'

以下是视图脚本：

create view temp_test_view_1 as
select
id,
id_description,
case when id_category = 'phone_id' then 'phone' else 'other' end as new_id_category
from (

select
1 as id,
'first id' as id_description,
null as id_category
from dummy

union all

select
2 as id,
'second id' as id_description,
'phone_id' as id_category
from dummy
) x
;

下面是我用来将视图左键联接到表，然后投影结果以查看测试表1中每个id对应的新\u id \u类别的查询：

select
t1.id,
t1.name,
t2.id,
t2.id_description,
t2.new_id_category
from test_table_1 t1
left join temp_test_view_1 t2 on t1.id = t2.id

输出为：

id   name,id   id_description new_id_category  
null 'd'  null null,          'other'          
1    'a'  1   'first id'      'other'          
2    'b'  2   'second id'     'phone'          
2    'c'  2   'second id'     'phone'         
3    'd'  null null           'other'

期望输出：

id   name,id   id_description new_id_category  
null 'd'  null null,          null          
1    'a'  1   'first id'      'other'          
2    'b'  2   'second id'     'phone'          
2    'c'  2   'second id'     'phone'        
3    'd'  null null           null

有人能解释一下查询产生的结果是否正确吗？如果是，为什么？我期望这个左连接在从视图中检索到的列上返回null，正如我期望的结果所示。
我没有在其他供应商提供的数据库系统中测试查询。
edit：我在sqlfiddle-mssql上测试了它，它产生了所需的输出。这里是链接：sqlfiddle.com/#！18/1c788/2年
如果有人需要代码本身来在ms sql中重现结果（而他将无法重现结果，而是得到所需的结果）：

create view temp_test_view_1 as
select
id,
id_description,
case when id_category = 'phone_id' then 'phone' else 'other' end as new_id_category
from (

select
1 as id,
'first id' as id_description,
null as id_category

union all

select
2 as id,
'second id' as id_description,
'phone_id' as id_category

) x
;

select *

into test_table_1

from (

select

1 as id,

'a' as name

union all

select

2 as id,

'b' as name

union all

select

2 as id,

'c' as name

union all

select

3 as id,

'd' as name

union all

select

null as id,

'd' as name

) x;
select
t1.id,
t1.name,
t2.id,
t2.id_description,
t2.new_id_category
from test_table_1 t1
left join temp_test_view_1 t2 on t1.id = t2.id