MySQLorderby uses instance analysis in union

In mysql, union is used to merge the result sets of two or more SELECT statements. orderby is used to sort the result sets according to certain rules. This article uses these two statements together, describes how to use orderby in union. The following are two original data tables used for the example test:

Articles:

Aid	Title	Content
1	Article 1	Article 1 body content...
2	Article 2	Article 2 body content...
3	Article 3	Article 3 body content...

Blog Log table:

Bid	Title	Content
1	Log 1	Log 1 body content...
2	Article 2	Article 2 body content...
3	Log 3	Log 3 body content...

Use order by in the UNION clause

Clause uses order by to sort the results of the SELECT clause first, and then aggregate the results of these clause queries.

If order by is used in a clause, parentheses () must be added to the entire clause due to priority issues and must be used with LIMIT:

(SELECT aid,title FROM article ORDER BY aid DESC LIMIT 10) UNION ALL(SELECT bid,title FROM blog ORDER BY bid DESC LIMIT 10)

The returned query result is as follows:

Aid	Title
3	Article 3
2	Article 2
1	Article 1
3	Log 3
2	Article 2
1	Log 1

Use order by in the UNION clause

If you want to use order by or LIMIT to classify or LIMIT all UNION results, parentheses should be added for a single SELECT statement, and order by or LIMIT should be placed behind the last one.

(SELECT aid,title FROM article) UNION ALL(SELECT bid,title FROM blog)ORDER BY aid DESC

The returned query result is as follows:

Aid	Title
3	Article 3
3	Log 3
2	Article 2
2	Article 2
1	Article 1
1	Log 1

Although the result of removing the parentheses in the SELECT clause is the same, we recommend that you do not omit the parentheses for clear statements.

Apply union order by to Alias

If the field alias is used in SELECT, oeder by must reference the alias:

(SELECT a AS b FROM table) UNION (SELECT ...) ORDER BY b