I noticed when I am combining two tables via union that the order is not what I expected.
calculus_class
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.