UNION causes "Conversion failed when converting the varchar value to int"

I tried to search for previous articles related to this, but I can't find one specific to my situation. And because I'm brand new to StackOverflow, I can't post pictures so I'll try to describe it.

I have two datasets. One is 34 rows, 1 column of all NULLs. The other 13 rows, 1 column of varchars.

When I try to UNION ALL these two together, i get the following error:

Conversion failed when converting the varchar value to data type int.

I don't understand why I'm getting this error. I've UNIONed many NULL columns and varchar columns before, among many other types and I don't get this conversion error.

Can anyone offer suggestions why this error occurs?

Solution

The error occurs because you have corresponding columns in the two of the subqueries where the type of one is an integer and the type of the other is a character. Then, the character value has -- in at least one row -- a value that cannot be automatically converted to an integer.

This is easy to replicate:

select t.*
from (select 'A' as col union all
      select 1
     ) t;

Here is the corresponding SQL Fiddle.

SQL Server uses pretty sophisticated type precedence rules for determining the destination type in a union. In practice, though, it is best to avoid using implicit type conversions. Instead, explicitly cast the columns to the type you intend.

EDIT:

The situation with NULL values is complicated. By itself, the NULL value has no type. So, the following works fine:

select NULL as col
union all
select 'A';

If you type the NULL, then the query will fail:

select cast(NULL as int) as col
union all
select 'A';

Also, if you put SQL Server in a position where it has to assign a type, then SQL Server will make the NULL an integer. Every column in a table or result set needs a type, so this will also fail:

select (select NULL) as col
union all
select 'A';

Perhaps your queries are doing something like this.