sql - UNION ALL 具有不同列类型的两个 SELECT - 预期行为？

Question

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

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 给出

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

但是标准中定义了什么？

Answer 1

来自T-SQL UNION页面:

The following are basic rules for combining the result sets of two queries by using UNION:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

当一种数据类型为 VARCHAR 而另一种数据类型为 INTEGER 时，SQL Server 将隐式尝试将 VARCHAR 转换为 INTEGER >(规则在优先级表中描述)。如果任何行转换失败，则查询失败。所以这有效:

INSERT INTO #tab1 VALUES(N'123'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2

但这并不:

INSERT INTO #tab1 VALUES(N'ABC'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2
-- Conversion failed when converting the varchar value 'ABC' to data type int.

转换规则如下所述:

T-SQL Data Type Precedence

<小时/>

话虽如此，您可以显式将整数数据转换为 varchar 以使查询正常运行(结果的数据类型将为 varchar)。