alex alex - 15 days ago 7
SQL Question

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

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

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.