SQL UNION Question

Can someone explain to me why the SQL statement:

SELECT 'TEST1'
开发者_高级运维UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

returns:

TEST2
TEST3
TEST1

I am trying to figure out the logic behind the UNION keyword in this aspect. Is there a way I could get it to return:

TEST1
TEST2
TEST3 

without using the ORDER BY clause? In other words, can I control the execution order of the UNION statements?

If it matters, I am using Postgre 9.0 and PHP as my language

Many thanks, Brett

---

According to the PostgreSQL docs for UNION:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned).

---

UNION semantics are that duplicates are removed. PostgreSQL is using a Hash function to remove the duplicates, and the results are comin out in the order of the key's hash.

You can use UNION ALL, but SQL still doesn't guarantee an order unless you use the ORDER BY clause.

EXPLAIN
SELECT 'TEST1'
UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

Produces:

HashAggregate  (cost=0.07..0.10 rows=3 width=0)
  ->  Append  (cost=0.00..0.06 rows=3 width=0)
        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)

Whereas

EXPLAIN
SELECT 'TEST1'
UNION ALL SELECT 'TEST2'
UNION ALL SELECT 'TEST3'

Produces:

Append  (cost=0.00..0.06 rows=3 width=0)
  ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)

---

Most databases do not guarantee the order of anything without an order by statement.

union in most cases could allow the database to operate all 3 queries in parallel and return the rows as fast as possible.