UNION ALL two SELECTs with different column types - expected behaviour?

Question

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 gives

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

but what is defined in the standard?

Answer 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

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.