Sample Code Analysis of MySQL Union syntax

CopyCode The Code is as follows: select...
Union [All | distinct]
Select...
[Union [All | distinct]
Select...]
Select... Union [All | distinct] Select... [Union [All | distinct] Select...]

Union is used to combine the results from many select statements into a result set. (If you want to merge and output the query results of multiple tables, for example, group messages and personal message tables are separated, but you can extract and display them together. You can use mysqlunion to join the query results)
The selected columns at the corresponding position of each select statement should have the same type (the precondition is that the two select columns must be of the same type !). (For example, the first column selected by the first statement should be of the same type as the first column selected by other statements .) The column name used in the first select statement is also the name of the Column Used for the result.
The SELECT statement is a regular selection statement, but is subject to the following restrictions:
Only the last SELECT statement can use into OUTFILE.
High_priority cannot be used with select statements that are part of the union operation. If you specify high_priority for the first select statement, it does not work. If you specify high_priority for other subsequent select statements, a syntax error occurs.
If you do not use the keyword all for union, all returned rows are unique, as if you have used distinct for the entire result set. If all is specified, all matched rows are obtained from all used select statements.
The distinct keyword is a self-selected word and does not play any role, but can be used in the syntax according to the requirements of the SQL standard. (In MySQL, distinct represents the default working nature of a Common Object .)
You can mix Union all and Union distinct in the same query. The mixed union type is treated in this way, that is, the distict shared body overwrites all the shared bodies located on the left. The distinct shared body can be explicitly generated using Union distinct, or implicitly generated using Union (without the distinct or all keyword.
If you want to use the order by or limit clause to classify or limit all Union results, parentheses should be added to a single SELECT statement, and put order by or limit behind the last one. The following example uses both clauses:
Code
(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 you want to implement paging, You can process the result set of the two queries as a large result set and then perform limit processing on the large result set !) Good ~!
(Select a from tbl_name where a = 10 and B = 1) mysqlunion (select a from tbl_name where a = 11 and B = 2) order by a limit 10;
This order by statement cannot be referenced by columns that contain table names (that is, names in tbl_name.col_name format. You can provide a column alias in the first select statement, and refer to the alias in order by, or use the column position to refer to the column in order. (Aliases are preferred because column locations are not recommended .)
In addition, if a column with a category has an alias, the order by clause must reference the alias instead of the column name. The first statement in the following statement must be run, but the second statement will fail. An error occurs when the 'A' column is unknown in 'order clause:
Code Copy code The Code is as follows: (select a as B from T) Union (Select...) order by B;
(Select a as B from T) Union (Select...) order by;
To apply order by or limit to an individual select,
Place the clause inside the parentheses that enclose the select:
(Select a as B from T) Union (Select...) order by B;
(Select a as B from T) Union (Select...) order by;
To apply order by or limit to an individual select,
Place the clause inside the parentheses that enclose the select:

To use order by or limit for a single SELECT statement, the clause should be placed in parentheses. Parentheses include select:
CodeCopy codeThe Code is 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 );
(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 );

2. instance Expansion
Mysqlunion can combine two queries of the same table. the benefits of doing so are also very obvious. For example, in the blog application, you can use an SQL statement to display the top blog and ordinary blog pages.
CodeCopy codeThe Code is as follows :(
Select *
From 'blog'
Where Top = 1
Order by created DESC
)
Union (
Select *
From 'blog'
Where Top = 0
Order by created DESC
) Limit 2, 3

The above content is an introduction to the mysqlunion syntax. I hope you will find some gains.