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

Question

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?

Answer 1

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.