Does Union All Guarantee the Order of the Result Set

Does UNION ALL guarantee the order of the result set

There is no inherent order, you have to use ORDER BY. For your example you can easily do this by adding a SortOrder to each SELECT. This will then keep the records in the order you want:

SELECT 'O', 1 SortOrder
UNION ALL
SELECT 'R', 2
UNION ALL
SELECT 'D', 3
UNION ALL
SELECT 'E', 4
UNION ALL
SELECT 'R', 5
ORDER BY SortOrder

You cannot guarantee the order unless you specifically provide an order by with the query.

Sql server union but keep order

Try this :-

Select * 
from
( 
Select name,surname, 1 as filter
from  Table1
Union all
Select name,surname , 2 as filter
from Table2
)
order by filter

Is the order of union all guaranteed

In your particular example, the order should not change because you're querying against the DUAL table and you won't have to worry about potential index changes from that particular query. So you will always get Foo then Bar back respectively.

However, in the real world, yes, the order can most certainly change -- depends on several factors such as table indexes, columns being returned, new data being introduced, etc. So if you want your results ordered in a particular way, you need to specify ORDER BY clause.

Hope this helps.

Keep ORDER BY after UNION

SELECT CON
FROM
(
SELECT 1 as SEQ, 0 as NR, NAME ,TEMP.CON1 as CON
FROM (
    SELECT NAME, CONCAT(NAME,'(', LEFT(OCCUPATION, 1),')') AS CON1
    FROM OCCUPATIONS
    ORDER BY NAME -- don't need 
    ) AS TEMP
UNION
SELECT 2, NR, null, TEMP2.CON2 
FROM (
    SELECT COUNT(*) AS NR, CONCAT('THERE ARE A TOTAL OF ', COUNT(OCCUPATION),' ', OCCUPATION, 's') AS CON2
    FROM OCCUPATIONS
    GROUP BY OCCUPATION
    ORDER BY NR, OCCUPATION -- don't need
) AS TEMP2 ) T 
ORDER BY SEQ, NR, NAME, CON

Tsql, union changes result order, union all doesn't

The only way to get a particular order of results from an SQL query is to use an ORDER BY clause. Anything else is just relying on coincidence and the particular (transitory) state of the server at the time you issue your query.

So if you want/need a particular order, use an ORDER BY.

---

As to why it changes the ordering of results - first, UNION (without ALL) guarantees to remove all duplicates from the result - not just duplicates arising from the different queries - so if the first query returns duplicate rows and the second query returns no rows, UNION still has to eliminate them.

One common, easy way to determine whether you have duplicates in a bag of results is to sort those results (in whatever sort order is most convenient to the system) - in this way, duplicates end up next to each other and so you can then just iterate over these sorted results and if(results[index] == results[index-1]) skip;.

So, you'll commonly find that the results of a UNION (without ALL) query have been sorted - in some arbitrary order. But, to re-emphasise the original point, what ordering was applied is not defined, and certainly shouldn't be relied upon - any patches to the software, changes in indexes or statistics may result in the system choosing a different sort order the next time the query is executed - unless there's an ORDER BY clause.

Is order preserved after UNION in PostgreSQL?

Basically, your query is incorrect to begin with. Use UNION ALL, not UNION or you would incorrectly remove duplicate entries. (There is nothing to say the trail cannot switch back and forth between the same emails.)

The Postgres implementation for UNION ALL returns values in the sequence as appended - as long as you do not add ORDER BY at the end or do anything else with the result.

_{Be aware though, that each SELECT returns rows in arbitrary order unless ORDER BY is appended. There is no natural order in tables.}

The same is not true for UNION, which has to process all rows to remove possible duplicates. There are various ways to determine duplicates, the resulting order of rows depends on the chosen algorithm and is implementation-dependent and completely unreliable - unless, again, ORDER BY is appended.

So use instead:

SELECT * FROM iter1
UNION ALL  -- union all!
SELECT * FROM iter2;

To get a reliable sort order, and "simulate the record of growth", you can track levels like this:

WITH RECURSIVE all_emails AS (
   SELECT  *, 1 AS lvl
   FROM    audit_trail
   WHERE   old_email = 'harold_gim@yahoo.com'

   UNION ALL  -- union all!
   SELECT t.*, a.lvl + 1
   FROM   all_emails  a
   JOIN   audit_trail t ON t.old_email = a.new_email
)
TABLE  all_emails
ORDER  BY lvl;

db<>fiddle here

_{Old sqlfiddle}

Aside: if old_email is not defined UNIQUE in some way, you can get multiple trails. You would need a unique column (or combination of columns) to keep it unambiguous. If all else fails you can (ab-)use the internal tuple ID ctid for the purpose of telling trails apart. But you should rather use your own columns. (Added example in the fiddle.)

• In-order sequence generation

Consider:

• How to return records in correct order in PostgreSQL

What is the difference between UNION and UNION ALL?

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

Union-all postgresql select clauses preserving order

Try this - allocate the queries' results into a temporary table.
Here it is step by step:

• Create a temporary table ex. the_temp_table like the the record type of <QUERY 1-1-1>

create temporary table the_temp_table as <QUERY 1-1-1> limit 0;

• Add an auto-increment primary key column extra_id to the_temp_table

alter table the_temp_table add column extra_id serial primary key not null;

• Then run all your queries one by one in the right order

insert into the_temp_table <QUERY 1-1-1>; insert into the_temp_table <QUERY 1-1-2>;
insert into the_temp_table <QUERY 1-1-3>; insert into the_temp_table <QUERY 1-1-4>;

insert into the_temp_table <QUERY 1-2-1>; insert into the_temp_table <QUERY 1-2-2>;
insert into the_temp_table <QUERY 1-2-3>; insert into the_temp_table <QUERY 1-2-4>;

-- continue 

• Finally

select <fields list w/o extra_id> from the_temp_table order by extra_id;

-- no sorting is taking place here

Effectively thus you will be emulating UNION ALL in a controlled manner with an insignificant performance penalty.

Using different order by with union

This should work:

SELECT * 
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
      FROM A
      ORDER BY [Price]) AS A1

UNION ALL

SELECT * 
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
      FROM A
      ORDER BY [Name]) AS A2

ORDER BY Ordinal

From MSDN:

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY
is allowed only at the end of the statement. This restriction applies
only to when you specify UNION, EXCEPT and INTERSECT in a top-level
query and not in a subquery.

Edited: to force the order you need to apply an ORDER BY to the outer query. I've added a constant value column to both queries.

---

Related Topics