Basic Warning About UNION
Published: Apr 17, 2015
I was looking for some sample code to help me with a personal C# application I was coding and in looking around I saw a perfect example as to what not to do when manipulating a database.

To be honest the C# code itself was pretty ropey, but aside from that I could see what the guy was trying to achieve but the t-SQL he was using would likely let him down at some point due to his misuse of “UNION”.

Basically what was happening was that the developer was pulling some data out of SQL Server to populate an ArrayList in C# and then iterating through the ArrayList.

This was fine (ignoring that he would have been better pulling through a whole table and using that) except that he was clearly working under the misconception that UNION forces order upon insert.

I’ve created the following example to show what I mean:

This is an incredibly pointless piece of code, it’s simply showing the technique that was being used by this particular dev online…

use AdventureWorks2012
go

declare @salesOrderID int

set
@salesOrderID =
(
  
select min(salesOrderID)
  
from Sales.SalesOrderHeader
)

select SalesOrderNumber
from sales.SalesOrderHeader
where SalesOrderID = @salesOrderID

union

select
c.AccountNumber
from sales.Customer c
join sales.SalesOrderHeader h
on c.CustomerID = h.CustomerID
where h.SalesOrderID = @salesOrderID


You can see what is being attempted here… the theory is that he’s making a single column table in order to populate his ArrayList and in doing so he believes he’s (using my example) inserting the SalesOrderNumber first, then the Customer Account Number second…

However, this isn’t the case. SQL Server has no inclination to maintain order when using a UNION. The results I saw when running the above code were as follows:

There are only 2 results here and yet they’re still in the “wrong” order. This would break the C# code in question.

In order to maintain order in this example you need to force it…

use AdventureWorks2012
go

declare @salesOrderID int

declare
@output table
(
  
id tinyint identity(1, 1),
  
data varchar(15)
)

set @salesOrderID =
(
  
select min(salesOrderID)
  
from Sales.SalesOrderHeader
)

insert into @output
select SalesOrderNumber
from sales.SalesOrderHeader
where SalesOrderID = @salesOrderID

insert into @output
select c.AccountNumber
from sales.Customer c
join sales.SalesOrderHeader h
on c.CustomerID = h.CustomerID
where h.SalesOrderID = @salesOrderID

select data
from @output
order by id


This will therefore provide the results as required.

As I mentioned, the example above isn’t particularly common (in the case of C# applications) but it could be a valuable lesson to anyone using a UNION under the belief that it will preserve order.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron