Using union and order by clause in mysql

When you use an ORDER BY clause inside of a sub query used in conjunction with a UNION mysql will optimise away the ORDER BY clause.

This is because by default a UNION returns an unordered list so therefore an ORDER BY would do nothing.

The optimisation is mentioned in the docs and says:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

The last sentence of this is a bit misleading because it should have an effect. This optimisation causes a problem when you are in a situation where you need to order within the subquery.

To force MySQL to not do this optimisation you can add a LIMIT clause like so:

(SELECT 1 AS rank, id, add_date FROM my_table WHERE distance < 5 ORDER BY add_date LIMIT 9999999999)
UNION ALL
(SELECT 2 AS rank, id, add_date FROM my_table WHERE distance BETWEEN 5 AND 15 ORDER BY rank LIMIT 9999999999)
UNION ALL
(SELECT 3 AS rank, id, add_date from my_table WHERE distance BETWEEN 5 and 15 ORDER BY id LIMIT 9999999999)

A high LIMIT means that you could add an OFFSET on the overall query if you want to do something such as pagination.

This also gives you the added benefit of being able to ORDER BY different columns for each union.

i was trying (order by after union) for below query, but couldn't:

select table1.*,table2.* 
from table1 
left join table2 on table2.id=0 
   union
select table1.*,table2.* 
from table2 
left join table1 on table2.i=table1.id
order by table1.id;

whatever your query is,
just add a same name alias in column, in all your select union queries
in above example it will be:

select table1.id as md,table1.*,table2.* 
from table1 
left join table2 on table2.id=0 
   union
select table1.id as md,table1.*,table2.* 
from table2 
left join table1 on table2.i=table1.id
order by md;

We can use ORDER BY clause with UNION result, after researching for a long time, I finally came to a solution.

In MySQL if you use parenthesis then scope of query is limited to parenthesis, if you want to sort the UNION result data coming from two or more complex queries use all SELECT and UNION statement in one line and in ORDER BY clause use the name of column you want to sort.

EX: SELECT * FROM customers UNION SELECT * FROM users ORDER BY name DESC

If you are using JOIN in SELECT queries than use only column name only not with the table variable name

EX: SELECT c.name,c.email FROM customers as c JOIN orders as o ON c.id=o.id UNION SELECT u.name,u.email FROM users as u JOIN inventory as i ON u.id=i.id ORDER BY name DESC

Just use order by column number (don't use column name). Every query returns some columns, so you can order by any desired column using it's number.

My favorite solution is to create a CTE

WITH cte as (
(SELECT * FROM table1)

UNION ALL 

(SELECT * FROM table2)
)
SELECT * 
FROM cte 
ORDER BY col1