Skip to content
Advertisement

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

stats_class

When I combine the two tables above

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

This is the result I received using DBeaver PostgreSQL:

Advertisement

Answer

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

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:

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement