sql server - SQL Union causes "Conversion failed when converting the varchar value to int" -

i tried search previous articles related this, can't find 1 specific situation. , because i'm brand new stack overflow, can't post pictures i'll try describe it.

i have 2 datasets. 1 34 rows, 1 column of nulls. other 13 rows, 1 column of varchars.

when try union these 2 together, "conversion failed when converting varchar value data type int." don't understand why i'm getting error. i've unioned many null columns , varchar columns before, among many other types , don't conversion error. can offer suggestions why error occurs?

the error occurs because have corresponding columns in 2 of subqueries type of 1 integer , type of other character. then, character value has -- in @ least 1 row -- value cannot automatically converted integer.

this easy replicate:

select t.* (select 'a' col union       select 1      ) t; 

here corresponding sql fiddle.

sql server uses pretty sophisticated type precedence rules determining destination type in union. in practice, though, best avoid using implicit type conversions. instead, explicitly cast columns type intend.

edit:

the situation null values complicated. by itself, null value has no type. so, following works fine:

select null col union select 'a'; 

if type null, query fail:

select cast(null int) col union select 'a'; 

also, if put sql server in position has assign type, sql server make null integer. every column in table or result set needs type, fail:

select (select null) col union select 'a'; 

perhaps queries doing this.