Copy Code code as follows:
SELECT ...
UNION [All | DISTINCT]
SELECT ...
[UNION [All | DISTINCT]
SELECT ...]
SELECT ... UNION [All | DISTINCT] SELECT ... [UNION [All | DISTINCT] SELECT ...]
The UNION is used to combine the results from many SELECT statements into a single result set. (If you want to combine query results from multiple tables, for example, a group message is separate from a personal message table, but you want to extract it and display it together.) Through the Mysqlunion joint query can be.
The selected columns that are listed in the corresponding position of each SELECT statement should have the same type (provided that the column type of two select stays the same!). ）。 (for example, the first column selected by the first statement should have the same type as the first column selected by other statements.) The column name used in the first SELECT statement is also used for the column name of the result.
The SELECT statement is a regular selection statement, but is qualified as follows:
Only the last SELECT statement can use into outfile.
High_priority cannot be used concurrently with a SELECT statement that is part of a union. If you specify high_priority for the first select, it will not work. If you specify high_priority for other subsequent SELECT statements, a syntax error is generated.
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 you specify all, you get all the matching rows from all the used SELECT statements.
Distinct keyword is an optional word, does not play any role, but according to the requirements of the SQL standard, in the syntax to allow the adoption. (in MySQL, distinct represents the default working nature of a common body.) ）
You can mix UNION all and union DISTINCT in the same query. The mixed union type is treated in this manner, that is, the distict common body overwrites all the all shared bodies located on its left. The distinct common body can be explicitly generated using union distinct or implicitly generated using union (followed by no distinct or all keywords).
If you want to categorize or limit all union results by using an ORDER BY or limit clause, you should add parentheses to a single SELECT statement and place the order by or limit after the last one. The following examples use these two clauses at the same time:
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 pagination, you can do this by treating the result set of two queries as a large result set and then limit processing of this large result set!) Well, 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 cannot use a column reference that includes a table name (that is, a name in tbl_name.col_name format). You can provide a column alias in the first SELECT statement, see the alias in order by, or use the column position to refer to the column in an order by. (aliases are preferred, because column locations are not recommended.) ）
In addition, if a column with a taxonomy has an alias, the ORDER BY clause must reference the alias, not the column name. The first statement in the following statement must be run, but the second will fail, appearing in ' order clause ' with an unknown column ' a ' ERROR:
Code
Copy Code code as follows:
(SELECT A as B from T) UNION (SELECT ...) Order by B;
(SELECT A as B from T) UNION (SELECT ...) Order by A;
To apply order BY or LIMIT to a 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 A;
To apply order BY or LIMIT to a individual SELECT,
Place the clause inside the parentheses that enclose the SELECT:
To use order by or limit for a single select, insert the sentence in parentheses. The parentheses contain a select:
Code
Copy Code code 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);
Two instance extensions
Mysqlunion can unite two queries on the same table. The benefits are also very obvious, such as in the blog application, you can use a SQL statement to achieve the top blog and the regular blog page display.
Code
Copy Code code 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 related content is the introduction of the Mysqlunion grammar, hope you can have some harvest.
