CODEWITHSUNDEEP
oracle-databaseunion
Soon
Soon

Reputation: 501

Oracle UNION have built- in sorting ability?

I've union the result of two subqueries.
1) Oracle's UNION sorts merged table in ascending order automatically?
2) I didn't specify alias M at the subquery. Is it legal?

CREATE TABLE T4 (
C0 VARCHAR2(10),
C1 VARCHAR2(10),
C2 NUMBER
);
INSERT INTO T4  VALUES ('a','abd',1);
INSERT INTO T4  VALUES ('a','abd',2);
INSERT INTO T4  VALUES ('a','abe',3);
INSERT INTO T4  VALUES ('a','abe',4);
INSERT INTO T4  VALUES ('a','ace',5);
INSERT INTO T4  VALUES ('a','ace',6);

INSERT INTO T4  VALUES ('b','abd',1);
INSERT INTO T4  VALUES ('b','abd',2);
INSERT INTO T4  VALUES ('b','abe',3);
INSERT INTO T4  VALUES ('b','abe',4);
INSERT INTO T4  VALUES ('b','ace',5);
INSERT INTO T4  VALUES ('b','ace',6);



SELECT ROWNUM,rr.C0,rr.M, rr.ss
FROM
(
SELECT C0,C1 M, SUM(C2) ss FROM T4  GROUP BY C0, C1
UNION
SELECT C0,SUBSTR(C1,1,1)||'__'  , SUM(C2) ss FROM T4  GROUP BY C0,SUBSTR(C1,1,1)  --I didn't specify alias M. Is it legal?
) rr
ORDER BY rr.C0,rr.M ASC; --Without this, the same result shows. So, Oracle UNION sorts merged table in ascending order automatically?

Upvotes: 0

Views: 155

Answers (1)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

  1. No. Oracle uses internal mechanisms to union two queries and remove duplicates. This might include sorting as well, but it's fully up to optimizer to decide. So never rely on that. Provide an ORDER BY clause if you want the result to be sorted.

  2. Yes, it's legal. Oracle names columns in the resulting dataset using the names of the first query in UNION statement.

Upvotes: 2

Related Questions