UNION ALL 两个具有不同列类型的 SELECTs - 预期行为?

问题描述

当我们对具有不同数据类型的两个表执行 UNION 时，由于 SQL Standard 的预期行为是什么:

What is the expected behaviour due to SQL Standard when we perform UNION on two tables with different data types:

create table "tab1" ("c1" varchar(max));
create table "tab2" ("c3" integer);
insert into tab1 values(N'asd'), (N'qweqwe');
insert into tab2 values(123), (345);
select
c_newname as myname
from
(
select "c1" as c_newname from "tab1"
union all
select "c3" from "tab2"
) as T_UNI;

MS SQL Server 给出

将 varchar 值 'asd' 转换为数据类型时转换失败内部

Conversion failed when converting the varchar value 'asd' to data type int.

但是标准中定义了什么?

but what is defined in the standard?

推荐答案

如果你想在每个查询中使用 union all 列需要具有相同的类型.C3必须转换为 varchar，因为 c1 是 varchar.尝试以下解决方案

If you want to use union all columns in every query need to have the same type.C3 must be converteted to varchar because c1 is varchar. Try below solution

create table "tab1" ("c1" varchar(max));
create table "tab2" ("c3" integer);
insert into tab1 values(N'asd'), (N'qweqwe');
insert into tab2 values(123), (345);
select
c_newname as myname
from
(
select "c1" as c_newname from "tab1"
union all
select cast("c3"  as varchar(max)) from "tab2"
) as T_UNI;

我用 "tab1" 替换了 "tab3" - 我认为这是错字.

I replaced "tab3" with "tab1" - I think it's typo.