If I do 4 selects in order 1, 2, 3, 4, using UNION ALL, the results come back 1, 4, 3, 2. Why?

I can use ORDER BY to rearrange the order, but why is it in this seemingly "out of order" order by default?

Use this table:

CREATE TABLE "AACoupons" (
"AACouponID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"CouponAmount" INTEGER NULL,
"AAItemID" INTEGER NULL,
PRIMARY KEY ( "AACouponID" ASC )
) ;

INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(200,2,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(201,4,3);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(202,6,3); 
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(500,162,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(501,NULL,NULL);
INSERT INTO "AACoupons" ("AACouponID","CouponAmount","AAItemID") VALUES(506,NULL,NULL);

Use this SELECT block:

select AACouponID as ID, 'ID1' as "Type" from aacoupons
where ID = 201
union all
select AACouponID as ID, 'ID2' as "Type" from aacoupons
where ID = 201
union all
select AACouponID as ID, 'ID3' as "Type" from aacoupons
where ID = 201
 union all
select AACouponID as ID, 'ID4' as "Type" from aacoupons
where ID = 201

Results:

ID,Type
201,'ID1'
201,'ID4'
201,'ID3'
201,'ID2'

asked 07 Dec '10, 20:05

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

retagged 18 Nov '12, 10:20

Nica%20_SAP's gravatar image

Nica _SAP
866722

Version 12.0 EBF 2601

(07 Dec '10, 22:26) Siger Matt

Your query won't run as submitted. Remove the semi-colon after the third select statement.

You can't depend on the results of any query unless you tell it how you want the result set to be sorted. To order a set of union-ed selects, add ORDER BY column number(s) at the end. Your new query would be:

select AACouponID as ID, 'ID1' as "Type" from aacoupons
where ID = 201
union all
select AACouponID as ID, 'ID2' as "Type" from aacoupons
where ID = 201
union all
select AACouponID as ID, 'ID3' as "Type" from aacoupons
where ID = 201
union all
select AACouponID as ID, 'ID4' as "Type" from aacoupons
where ID = 201
ORDER BY 2  -- added clause
permanent link

answered 07 Dec '10, 20:58

SethKrieger's gravatar image

SethKrieger
88116
accept rate: 100%

edited 08 Dec '10, 08:26

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646

Fixed the semi-colon. I realize that you can use order by to force order on the results. I just wondered why without me forcing them to be ordered they would not appear in the same order as the select statements.

(07 Dec '10, 21:46) Siger Matt

@Siger: Well, that's the effect of query optimization. The engine is not at all bound to execute the different branches in the order you or me would expect:)

(08 Dec '10, 08:28) Volker Barth
Comment Text Removed
Comment Text Removed
1

@SethKrieger: It's funny, folks have been giving the "you can't depend" answer for years, and it's finally coming true... the query engine is finally using the Infinite Improbability Drive even on cases like this :)

(08 Dec '10, 13:38) Breck Carter
1

@Breck, so you think Sybase will skip the unlucky version 13 and rename the product "Heart of Gold?"

(08 Dec '10, 13:54) Siger Matt
1

@Volker: I suppose before this discussion I would have asserted that the execution of the branches could be in any order for the purpose of the optimization, but that the returning of the results would be independent of the execution and would be in the order of the statements, unless I used ORDER BY. Clearly I would have been wrong. Execution and returning results seem to be the same thing.

(08 Dec '10, 14:35) Siger Matt

Unlike some other languages - e.g. C/C++ - the SQL language does not guarentee the order of operations taken to execute a statement. As such, the SQL optimizer and execution engine is allowed to perform the operations in any order that it sees fit, presumably in an attempt to execute the statement as quickly as possible.

You did not specify the version (and build number) that you are using so it is difficult to determine why you got the output that you did - possibly due to a parallel execution plan or perhaps an artifact in the way the statement is parsed - but as Seth has mentioned, the only way that you can guarantee the ordering of the output is to use an ORDER BY clause.

permanent link

answered 07 Dec '10, 21:57

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×10
×10
×5

question asked: 07 Dec '10, 20:05

question was seen: 1,156 times

last updated: 18 Nov '12, 10:20