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?

Solution 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.

Solution 2

From T-SQL UNION page:

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.

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:

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

---

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).

Solution 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.

SQL Standard:

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.