The method used by union and order by in MySQL

The Union and order by in MySQL can be used together, but there are some minor problems to be aware of in use, as illustrated by the examples below. First look at the T1 table below.

1, if directly with the following SQL statement will be error: incorrect usage of UNION and ORDER by.

SELECT * from T1 WHERE username like ' l% ' ORDER by score ASC
UNION
SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC

Because the Union can use only one order by without parentheses, there are 2 ways to modify this statement. As follows:

(1) The previous order by can be removed and changed to the following:

SELECT * from T1 WHERE username like ' l% '
UNION
SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC

The SQL means union first and then order by for the entire result set.

(2) can be changed to the following by two queries, respectively, by parentheses:

(SELECT * from T1 WHERE username like ' l% ' ORDER by Sroce ASC)
UNION
(SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC)

The purpose of this approach is to have the two result sets first order by, and then union the two result sets. But you will find that this is not an error, but the two order by does not work, so it should be changed as follows:

SELECT * FROM
(SELECT * from T1 WHERE username like ' l% ' ORDER by score ASC) T3
UNION
SELECT * FROM
(SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC) T4

In other words, an order by cannot appear directly in the Union clause, but it can appear in the clause of the clause.

2. By the way, the difference between Union and union all.

Union filters out duplicate rows in two result sets, and union all does not filter out duplicate rows.

The method used by union and order by in MySQL