LL - Union All - Conversion failed when converting the varchar value to data type int

SQL union between NULL and VARCHAR error


NULL is  a member of every data type.

When any SELECT query is being run,
the contents of each column must be of one type, and only one type.
When there are a mixture of values in a column (including NULLs),
the type can obviously be determined by examining the types of the non-NULL values,
and appropriate conversions are performed, as necessary.

But, when all rows contain NULL for a particular column,
and the NULL hasn't been cast to a particular type, then there's no type information to use.
So, SQL Server, somewhat arbitrarily, decides that the type of this column is int.