UNION ALL two SELECTs with different column types - expected behavior?
What is the expected behavior due to SQL Standard when executed 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 error while converting vardar 'asd' value to int data type.
but what is defined in the standard?
source to share
If you want to use columns union all
in every query, you must be of the same type. C3
must be converted to varchar because it 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 this is a typo.
source to share
From the T-SQL UNION page:
Following are the basic rules for combining result sets of two queries using UNION:
- The number and order of columns must be the same in all queries.
- Data types must be compatible.
If one is data type VARCHAR
and the other is INTEGER
, then SQL Server will implicitly try to convert VARCHAR
to INTEGER
( see the priority table for rules). If the conversion fails for any string, 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 is not the case:
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 conversion rules are described here:
Having said that, you can explicitly convert the integer data to varchar to make the query work (the data type of the result will be varchar).
source to share
General rule -> Either the data type used must be the same in the two tables (or) , you must use the cast or convert function to match the data types in those two tables .
SQL standard:
1) The number and order of the columns must be the same in all queries.
2) The data types of the columns must be compatible: they do not have to be of the same type, but they must be of a type that SQL Server can implicitly convert.
source to share