'union all in SQL (Postgres) mess the order

I have a query which is order by date , there is the query I have simplified it a bit but basically is :

select * from 
(select  start_date, to_char(end_date,'YYYY-mm-dd') as end_date from date_table
order by start_date ,end_date ) 
where start_date is null or end_date is null

It shows prefect order

but I add

union all
select start_date, 'single missing day' as end_date  from 
calendar_dates
where db_date>'2017-12-12' and db_date<'2018-05-13'

Then the whole order messed up. Why is that happened? Union or union all should just append the dataset from first query with the second, right? It should not mess the order in the first query, right?

I know this query doesn't makes any sense, but I have simplified it to show the syntax.

sqlpostgresqlunionunion-all


Solution 1:[1]

You can't predict what would be the order outcome by just assuming that UNION ALL will append queries in the order you write them.

The query planner will execute your queries in whatever order it sees it fit. That's why you have the ORDER BY clause. Use it !

For example, if you want to force the order of the first query, then the second, do :

select * from 
(select  1, start_date, to_char(end_date,'YYYY-mm-dd') as end_date from date_table
order by start_date ,end_date ) 
where start_date is null or end_date is null
union all
select 2, start_date, 'single missing day' as end_date  from 
calendar_dates
where db_date>'2017-12-12' and db_date<'2018-05-13' 
ORDER BY 1

Solution 2:[2]

You are mistaken. This query:

select d.*
from (select start_date, to_char(end_date,'YYYY-mm-dd') as end_date
      from date_table
      order by start_date, end_date
     ) d
where start_date is null or end_date is null

does not "show perfect order". I might just happen to produce the ordering that you want, but that is a coincidence. The only way to get results in a particular order is to use ORDER BY in the outermost SELECT. Period.

So, if you want results in a particular order, then use order by:

select d.*
from ((select d.start_date, to_char(end_date, 'YYYY-mm-dd') as end_date, 1 as ord
       from date_table d
       where d.start_date is null or d.end_date is null
       order by start_date, end_date
      ) union all
      (select cd.start_date, 'single missing day' as end_date, 2 as ord
       from calendar_dates cd
       where cd.db_date > '2017-12-12' and cd.db_date < '2018-05-13' 
      )
     ) d
order by ord, start_date;

Solution 3:[3]

UNION or UNION ALL will mess up the order in the first SELECT. Therefore, we can make a trick that we will re-order these columns in the Outer Select as below:

SELECT * FROM 
( 
     select colA, colB
          From TableA
          -- ORDER BY colA, colB --   
     UNION ALL   
     select colC, colD
         FROM TableB
         ORDER BY colC, colD 
) tb 
ORDER BY colA, colB

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 olleo
Solution 2 Gordon Linoff
Solution 3 Duc Toan Pham

Related Questions