SQLCE 2.0 and 'UNION' odd behaviour

  • Thread starter Thread starter chris-s
  • Start date Start date
C

chris-s

Reluctantly, I've opted to use a 'union' command to join together two
select statements.

However, I was pretty surprised with the results...

Query1 : "select ref from trans" returns 516 rows
Query2 : "select ref from history" returns 188 rows

Now this is where it goes weird...

Query3: "select ref from trans union select ref from history" returns
198 rows.

Looking at the rows it has returned I can see it has (a) sorted them
by ref ascending and (b) grouped them by ref

and finally..

Query4: "select ref,date from trans union select ref,date from
history" returns 355 rows.

This times it's done the same sorting as in query 3 but has also
grouped the results by ref and date.

Whilst this effect might actually be benficial for my purpose, it's
not what I would have expected so why does it do this? I can only
assume it's something to do with optimizing the result set to return
the minimum number of rows by removing duplicates, but thats bit of a
large assumption to make isn't it?

Chris
 
I wouldn't assume anything about record order.

If you want them ordered, add an "order by" after the unions

select ref "Col1" from trans
union
select ref from history
order by "Col1"

Just curious. Why where you reluctant to use the union?
 
Reluctantly, I've opted to use a 'union' command to join together two
select statements.

However, I was pretty surprised with the results...

Query1 : "select ref from trans" returns 516 rows
Query2 : "select ref from history" returns 188 rows

Now this is where it goes weird...

Query3: "select ref from trans union select ref from history" returns
198 rows.

Looking at the rows it has returned I can see it has (a) sorted them
by ref ascending and (b) grouped them by ref

and finally..

Query4: "select ref,date from trans union select ref,date from
history" returns 355 rows.

This times it's done the same sorting as in query 3 but has also
grouped the results by ref and date.

Whilst this effect might actually be benficial for my purpose, it's
not what I would have expected so why does it do this? I can only
assume it's something to do with optimizing the result set to return
the minimum number of rows by removing duplicates, but thats bit of a
large assumption to make isn't it?

Chris
Click to expand...

UNION's default behavior is to combine the queries and return the
DISTINCT values.
If you need to retrieve ALL values, simply use the UNION ALL instead.
 
Back
Top