Jasen's answer is correct - PostgreSQL is free to return the rows in any order it pleases unless you add an ORDER BY
clause like this:
(SELECT ... UNION SELECT ... UNION ALL SELECT ...) ORDER BY ...;
You need the parenthesis to make sure the ordering is applied to the whole result and not only to the last branch of the UNION
.
But let me explain why PostgreSQL doesn't return the rows in the order you expect. The reason is that the first UNION
is not UNION ALL
. If you had used UNION ALL
everywhere, PostgreSQL would execute the query like this:
EXPLAIN (COSTS OFF)
select id, nombre
from practicaleft
where nombre similar to 'A%'
union all
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright
where cumpleanios > current_date - 5;
QUERY PLAN
══════════════════════════════════════════════════════════════════
Append
-> Seq Scan on practicaleft
Filter: ((nombre)::text ~ '^(?:A.*)$'::text)
-> Seq Scan on practicaright pr
Filter: ((id = 4) OR ((apellido)::text ~~* '_o%'::text))
-> Seq Scan on practicaright
Filter: (cumpleanios > (CURRENT_DATE - 5))
(7 rows)
That is, PostgreSQL would execute the three queries and simply append the results, and you would end up with the ordering you expected.
But you used union
the first time, and union
eliminates duplicates. This is executed as follows:
EXPLAIN (COSTS OFF)
select id, nombre
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright
where cumpleanios > current_date - 5;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════
Append
-> HashAggregate
Group Key: practicaleft.id, practicaleft.nombre
-> Append
-> Seq Scan on practicaleft
Filter: ((nombre)::text ~ '^(?:A.*)$'::text)
-> Seq Scan on practicaright pr
Filter: ((id = 4) OR ((apellido)::text ~~* '_o%'::text))
-> Seq Scan on practicaright
Filter: (cumpleanios > (CURRENT_DATE - 5))
(10 rows)
PostgreSQL uses a hash aggregate to remove duplicates from the first two branches. The result rows are returned in the order they happen to have in the hash table, which is pretty random (good hash functions behave like that).