Skip to main content
deleted 143 characters in body
Source Link
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

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:

You need the parentheses to make sure the ordering is applied to the whole result and not only to the last branch of the UNION.

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:

You need the parentheses to make sure the ordering is applied to the whole result and not only to the last branch of the UNION.

Jasen's answer is correct - PostgreSQL is free to return the rows in any order it pleases unless you add an ORDER BY clause:

edited body
Source Link
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

You need the parenthesisparentheses to make sure the ordering is applied to the whole result and not only to the last branch of the UNION.

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.

You need the parentheses to make sure the ordering is applied to the whole result and not only to the last branch of the UNION.

Source Link
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

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