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

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, ORDER BY  in this context typically is used in conjunction with LIMIT , 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 has no effect in any case. 

To use an ORDER BY  or LIMIT  clause to sort or limit the entire UNION  result, parenthesize the individual SELECT  statements and place the ORDER BY  or LIMIT  after the last one: 

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

A statement without parentheses is equivalent to one parenthesized as just shown. 

Beginning with MySQL 8.0.19, you can use ORDER BY  and LIMIT  with TABLE  in unions in the same way as just shown, bearing in mind that TABLE  does not support a WHERE  clause. 

This kind of ORDER BY  cannot use column references that include a table name (that is, names in tbl_name . col_name  format). Instead, provide a column alias in the first SELECT  statement and refer to the alias in the ORDER BY . (Alternatively, refer to the column in the ORDER BY  using its column position. However, use of column positions is deprecated.) 

Also, if a column to be sorted is aliased, the ORDER BY  clause must  refer to the alias, not the column name. The first of the following statements is permitted, but the second fails with an Unknown column ’a’ in ’order clause’  error: 

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b; (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a; 

To cause rows in a UNION  result to consist of the sets of rows retrieved by each SELECT  one after the other, select an additional column in each SELECT  to use as a sort column and add an ORDER BY  that sorts on that column following the last SELECT : 

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col; 

To additionally maintain sort order within individual SELECT  results, add a secondary column to the ORDER BY  clause: 

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a; 

Use of an additional column also enables you to determine which SELECT  each row comes from. Extra columns can provide other identifying information as well, such as a string that indicates a table name. 

## UNION Restrictions 

In a UNION , the SELECT  statements are normal select statements, but with the following restrictions: 

• HIGH_PRIORITY  in the first SELECT  has no effect. HIGH_PRIORITY  in any subsequent SELECT produces a syntax error. 

