Tag: sql

Posted on

SQL union does not guarantee the order of the result set

I introduced a bug in a SQL script I wrote recently because I was unaware that SQL union did not guarantee the order of the returned result set.

The SQL stored procedure finds the primary and secondary network nodes that match a set of criteria. It looks something like:

select bd.name, n.nodeId, r.realmName
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on cn.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @location
union
select bd.name, n.nodeId, r.realmName
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on n.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @secLocation

I assumed the result returned by the first select would always be the first row of the final result set. (And the result from the second select would be the second row). In retrospect, it should have been obvious that this assumption was wrong. SQL never guarantees order unless the keyword order by is used!

To guarantee the order in a SQL union, an extra order column has to be used:

select bd.name, n.nodeId, r.realmName, 1 as nodeOrder
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on cn.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @location
union
select bd.name, n.nodeId, r.realmName, 2 as nodeOrder
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on n.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @secLocation
order by nodeOrder