Mysql database union and order by priority

Let's take a look at some problems and solutions for using the Mysql database union and order by priorities. I hope this article will be helpful to you.

In Mysql reference manual, the priorities of union and order by are not described.

The recommended method is to add () to the SQL statement to make the SQL syntax clearer.

For example, if order by is required for the result after union:

(SELECT a FROM tbl_name WHERE a = 10 and B = 1)

UNION

(SELECT a FROM tbl_name WHERE a = 11 and B = 2)

Order by a LIMIT 10;

If order by is required for a single SQL statement, the order by clause should be placed in parentheses as follows:

(SELECT a FROM tbl_name WHERE a = 10 and B = 1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM tbl_name WHERE a = 11 and B = 2 ORDER BY a LIMIT 10 );

Note: The order by clause used in parentheses for a single SQL statement takes effect only when combined with LIMIT. Otherwise, order by is optimized.

I tested the union and order by operations without adding () on two tables of the innodb engine, as follows:

SELECT a FROM tbl_name WHERE a = 10 and B = 1

UNION

SELECT a FROM tbl_name WHERE a = 11 and B = 2

Order by a LIMIT 10;

It is found that the default result is union first, and then order by, which is the same as the execution result in the first case.

However, it is better to add the corresponding () to make the logic clearer.

In addition, in Mysql, union can have three forms: union, union distinct, and union all.

Union and union distinct sort the results after union to ensure that all returned rows are unique.

Union all returns all matched rows in all SELECT statements.