联合所有两个选择与不同的列类型-预期行为?

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

当我们对具有不同数据类型的两个表执行联合时，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 gives

MS SQL Server提供了

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

当将varchar值“asd”转换为数据类型int时，转换失败。

but what is defined in the standard?

但是标准中定义了什么呢?

3 个解决方案

#1

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

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

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;

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

我把“tab3”换成了“tab1”——我想是打印错误。

#2

From T-SQL UNION page:

从t - sql联盟页面:

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

以下是使用UNION合并两个查询的结果集的基本规则:

The number and the order of the columns must be the same in all queries.

在所有查询中，列的数量和顺序必须相同。

The data types must be compatible.

数据类型必须是兼容的。

When one datatype is VARCHAR and other is INTEGER then SQL Server will implicitly attempt to convert VARCHAR to INTEGER (the rules are described in the precedence table). If conversion fails for any row, the query fails. So this works:

当一个数据类型为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

But this does not:

但这并不是:

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.

The rules for conversion are described here:

转换规则如下:

T-SQL Data Type Precedence

t - sql数据类型优先级

Having said that, you can explicitly convert your integer data to varchar in order to make the query work (the datatype of result would be varchar).

说到这里，您可以显式地将整数数据转换为varchar，以便使查询工作(结果的数据类型将是varchar)。

#3

The basic rule is--> Either the datatype used should be same in two table(or)you should use cast or convert function to match the datatypes in those two tables.

基本规则是——>或者使用的datatype应该在两个表中相同(或者)，您应该使用cast或convert函数来匹配这两个表中的数据类型。

SQL Standard:

SQL标准:

1)The number and the order of the columns must be the same in all queries.
2)Column datatypes must be compatible: They need not be the same exact same type, but they must be of a type that SQL Server can implicitly convert.

1)所有查询的列数和顺序必须相同。2)列数据类型必须是兼容的:它们不一定是相同的类型，但必须是SQL Server可以隐式转换的类型。

#1