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?

+3


source to share


3 answers


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.

+2


source


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:

T-SQL data type precedence


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

+4


source


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.

+1


source







All Articles