In MySQL, union, order by, and limit

I have a table

Create Table 'test1 '(
'Id' int (10) unsigned not null auto_increment,
'Name' varchar (20) not null,
'Desc' varchar (100) not null,
Primary Key ('id ')
) Engine = InnoDB default charset = utf8

 

(1) The following query will report an error: [err] 1221-Incorrect usage of union and order

Select * From test1 where name like 'a % 'order by name
Union
Select * From test1 where name like 'B %' order by name

Should be changed:

Select * From test1 where name like 'a %'
Union
Select * From test1 where name like 'B %' order by name

In Union, only one order by clause is allowed without parentheses (think about what if the column names of order by clause on both sides of union are different ), this sorts the Union result sets.

Or change:

(Select * From test1 where name like 'a % 'order by name)
Union
(Select * From test1 where name like 'B %' order by name)

The two order by statements are executed before Union.

 

(2) The same

Select * From test1 where name like 'a % 'limit 10
Union
Select * From test1 where name like 'B %' Limit 20

Equivalent

(Select * From test1 where name like 'a % 'limit 10)
Union
(Select * From test1 where name like 'B %') Limit 20

That is, the last limit acts on the result set after union, rather than the SELECT statement after union.

You can also add parentheses to get the expected results.

(Select * From test1 where name like 'a % 'limit 10)
Union
(Select * From test1 where name like 'B %' Limit 20)

 

(3) Difference between Union and Union all

Union filters out duplicate rows in the result set obtained by select on both sides of union, while union all does not filter out duplicate rows.