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 th

Solution 1:

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).

Solution 2:

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'UNIONSELECT'TEST2'UNIONSELECT'TEST3'

Produces:

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

Whereas

EXPLAIN
SELECT'TEST1'UNIONALLSELECT'TEST2'UNIONALLSELECT'TEST3'

Produces:

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

Solution 3:

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.