CODEWITHSUNDEEP
sqlpostgresqlselectsql-order-byunion
Kay
Kay

Reputation: 345

SQL's union default order by clause?

I noticed when I am combining two tables via union that the order is not what I expected.

calculus_class

Kevin   Le      [email protected]
Jackie  Chan    [email protected]
Sam     Smit    [email protected]

stats_class

Kay     Lam     [email protected]
Jackie  Chan    [email protected]
Pooja   Pri     [email protected]

When I combine the two tables above 

select * from calculus_class
union 
select * from stats_class

I expect the results to be in order from top to bottom like this:

Kevin   Le      [email protected]
Jackie  Chan    [email protected]
Sam     Smit    [email protected]
Kay     Lam     [email protected]
Jackie  Chan    [email protected]
Pooja   Pri     [email protected]

This is the result I received using DBeaver PostgreSQL:

Kevin   Le      [email protected]
Pooja   Pri     [email protected]
Jackie  Chan    [email protected]
Sam     Smit    [email protected]
Kay     Lam     [email protected]

Upvotes: 0

Views: 421

Answers (3)

Alexander
Alexander

Reputation: 139

Nested query is not necessary:

select 1 rn, c.* from calculus_class c union all select 2 rn, s.* from stats_class order by 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271061

Actually, you are using union which removes duplicates, but you don't want duplicate removal. So just use union all:

select * from calculus_class
union all
select * from stats_class;

If you did want to order the results, you need to remember that SQL tables and result sets represent unordered sets. If you did want the results ordered by something, you could add an order by:

select * from calculus_class
union all
select * from stats_class
order by fname;

Here is a db<>fiddle.

Upvotes: 3

GMB
GMB

Reputation: 222682

You seem to assume that table rows have an inherent ordering. This is not the case. Tables are mode of unordered sets of rows, there is no default ordering whatsoever.

Unless you do add an order by clause to your query, the ordering in which rows will be returned is undefined: the database is free to return rows in whatever order it likes - this might, or might not, be consistent over consecutive executions of the very same query. Same goes for rows returned by a union query (which actually creates a derived table).

So, if you want ordering, do use an order by clause: it is unclear which column should be used so I put ? in the query.

select *
from (
    select * from calculus_class
    union all
    select * from stats_class
) t
order by  ?

If you want rows from the first table first, you can do:

select *
from (
    select 1 rn, c.* from calculus_class c
    union all
    select 2 rn, s.* from stats_class
) t
order by  rn

Note that I changed union to union all: unless you do want to eliminate duplicates across the two tables (which is the purpose of union), union all is more efficient and should always be used.

Upvotes: 2

Related Questions