I noticed when I am combining two tables via union that the order is not what I expected.
calculus_class
x
Kevin Le kevinle892@email.edu
Jackie Chan jchan@email.edu
Sam Smit ssmitten@email.edu
stats_class
Kay Lam klam204@email.edu
Jackie Chan jchan@email.edu
Pooja Pri ppriyanka@email.edu
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 kevinle892@email.edu
Jackie Chan jchan@email.edu
Sam Smit ssmitten@email.edu
Kay Lam klam204@email.edu
Jackie Chan jchan@email.edu
Pooja Pri ppriyanka@email.edu
This is the result I received using DBeaver PostgreSQL:
Kevin Le kevinle892@uci.edu
Pooja Pri ppriyanka@uci.edu
Jackie Chan jchan473@uci.edu
Sam Smit ssmitten@uci.edu
Kay Lam klam204@uci.edu
Advertisement
Answer
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.