Union All problem

Database
Enthusiast

Union All problem

Can anyone explain the following odd results?

When I execute the following query:

select 'x' ,count(*) from dbc.databases
union all
select 'x1' ,count(*) from dbc.databases

I get the following results:

x 193
x 193

but when I run it this way:

select 'x1' ,count(*) from dbc.databases
union all
select 'x' ,count(*) from dbc.databases

I get the following results:

x1 193
x 193

I don't understand why the literal is change in the first example. This seems to only occur when the literal from the first is contained in the second literal. It also has interesting results with '1x', '3xa','bfgfxj234'....
2 REPLIES
Junior Contributor

Re: Union All problem

Hi John,
SQL Reference: Functions and Operators
Chapter 5: Set Operators
Attributes of a Set Result

"The data type, title, and format clauses contained in the first SELECT statement determine the
data type, title, and format information that appear in the final result."

The datatype of 'x' is a varchar(1) :-)

Dieter
N/A

Re: Union All problem

Hi,

Could someone help in understanding the performance impacts of using UNION ALL in a sql query?

I have a huge query and when I run it with UNION ALL it fails with spool space issue whereas the same query is getting executed successfully without any issues with only only first half of the UNION ALL.

If UNION ALL is a costly operation, what is the cost effective alternative to it?