when you do Union all, does the order of the columns matter? (sql server 2008))

Question

Post reply

when you do Union all, does the order of the columns matter? (sql server 2008))

applebutton

Mr or Mrs. 500

Points: 503
More actions
July 14, 2011 at 6:45 pm

#389981

when you do Union all, does the order of the columns matter?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Simply put .. Yes it does

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

rgtft Hall of Fame Points: 3518 More actions · Answer 2

As Ron said, "Yes". You can prove this to yourself by playing around with something like:

DECLARE @Animal1 table (name varchar(20), weight smallint)

INSERT @Animal1 (name, weight) VALUES ('Gazelle', 250),

('Eland', 300),

('Gnu', 450)

DECLARE @Animal2 table (name varchar(20), weight smallint)

INSERT @Animal2 (name, weight) VALUES ('Toad', 2),

('Aligator', 100),

('Salamander', 1)

--

SELECT name, weight

FROM @Animal1

UNION ALL

SELECT weight, name

FROM @Animal2

This code will give an error as you are trying to put a string (varchar) into a number (smallint). The union statement puts the two selects together by order, not by name.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The order has to be the same in all of the queries you're unioning together. Beyond that, the general order of the columns in the query as a whole doesn't matter.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jwbart06 Hall of Fame Points: 3291 More actions · Answer 4

Weird thing you don't have to have the same size of data type.

If you were to explicitly convert the columns to be the same datatype you can, but as you can see

the size I used varchar(8) instead of 20.

SELECT name as 'name', cast(weight as varchar(8)) as 'name'

FROM @Animal1

UNION ALL

SELECT cast(weight as varchar(8)) as 'name2', name as 'weight2'

FROM @Animal2

I believe the main issue is conversion , it is not possbile to convert 'Toad' to an int, if we replace all names with numbers , but leave them as characters it works.

So it doesn't matter which order, it matters how it gets explicity converted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jwbart06 (7/15/2011)
Weird thing you don't have to have the same size of data type.

No, they just have to be implicitly convertable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass