Mysql Federated Query Union and UNION All's use introduction _mysql

The function and grammar of union and UNION all

UNION is used to merge the result sets of two or more SELECT statements and eliminate any duplicate rows in the table.
The SELECT statement within the UNION must have the same number of columns, and the column must have a similar data type.
Also, the order of the columns in each SELECT statement must be the same.
SQL UNION Syntax:

Copy Code code as follows:

SELECT column_name from table1
UNION
SELECT column_name from Table2

Note: By default, the UNION operator picks a different value. If duplicate values are allowed, use UNION all.
Do not eliminate duplicate rows when all is used with union (that is, union ALL)
SQL UNION All syntax

Copy Code code as follows:

SELECT column_name from table1
UNION All
SELECT column_name from Table2

Note: In addition, the column names in the union result set are always equal to the column names in the first SELECT statement in the Union.
Note: 1, the column names in the UNION result set are always equal to the column names in the first SELECT statement
2. The SELECT statement within the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same

II. usage of union and matters needing attention

Union: The meaning of union, that is, to combine two or more query results.
Requirements: Two queries must have the same number of columns
Recommended: Column types can be different, but the recommended query for each column, like the corresponding type to the same
Data that can come from multiple tables: the names of the columns that are removed by multiple SQL statements can be inconsistent, and the column name of the first SQL statement is the same.
If the rows that are fetched in different statements are exactly the same (where the values for each column are the same), then union merges the same rows and ends with only one row. It can also be understood that the Union will remove the duplicate rows.
If you do not want to remove duplicate rows, you can use union ALL.
If there is an order by,limit in the clause, enclose the parentheses (). After all the clauses are recommended, the result of the final merge is sorted or filtered.
Such as:

Copy Code code as follows:

(SELECT * from a-order by ID) union (SELECT * FROM B-order ID);

In clauses, the order by needs to be used in conjunction with limit to make sense. If not used with limit, it is removed when parsing is optimized by the parser.

Iii. Examples of learning

The original table used in the following example:
Employees_china:

Copy Code code as follows:

e_id E_name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming

Employees_usa:

Copy Code code as follows:

e_id E_name
Adams, John
Bush, George
Carter, Thomas
Gates, Bill.

Using the UNION command instance

List all the different employee names in China and the United States:

Copy Code code as follows:

SELECT E_name from Employees_china
UNION
SELECT E_name from Employees_usa

Results:

Copy Code code as follows:

E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Gates, Bill.

Note: This command does not list all employees in China and the United States. In the example above, we have two employees with the same name, and only one of them is listed. The UNION command selects only a different value.

Using the UNION all command instance

The Union ALL command and the Union command are almost equivalent, but the Union ALL command lists all values.

Copy Code code as follows:

SQL Statement 1
UNION All
SQL Statement 2

Instance:
List all employees in China and the United States:

Copy Code code as follows:

SELECT E_name from Employees_china
UNION All
SELECT E_name from Employees_usa

Results

Copy Code code as follows:

E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Carter, Thomas.
Gates, Bill.

Iv. Examples of project use

Web projects often encounter the problem of whole-site search, that is, customers want to enter a word in the site's search box, and then throughout the site as long as the page containing the word will appear in the search results. Because a Web project cannot be done with a single table, it is common to use Union search to solve the whole problem.

The following is an enumeration of the SQL statements used for the Union search:

Copy Code code as follows:

SELECT * FROM

(SELECT ' id ', ' subject ' article ' WHERE ' active ' = ' 1 ' and ' subject ' like '% to adjust picture% ' order by ' add_time ' DESC)

as T1

UNION ALL

SELECT * FROM

(SELECT ' id ', ' class_name ' as ' subject ' from ' Web_class ' WHERE ' active ' = ' 1 ' and ' class_name ' like '% to adjust picture% ' order by ' Class_ Id ' DESC)

As T2

Union

SELECT * FROM

(SELECT ' id ', ' subject ' from ' article ' WHERE ' active ' = ' 1 ' and "(' subject ' like '% '/' subject ' like '% picture% ') Order BY ' Ad D_time ' DESC)

as T3;

The Union all and Union are the main uses of the combined query for the above SQL statements, and the difference is that union all enumerates the results of all eligible queries, and Union will filter out the duplicate results by doing all the query results that match the criteria.

The explanation for the above SQL statements is that the article table and the Web_class table are divided into two different tables, so there is no need to eliminate duplicate results. However, the third branch of the joint query of the SQL query is a combination of the query statement, and the result of this SQL statement query is sure to contain the first branch of the query results of the SQL statement, here is not necessary, so did not use all to remove duplicate query results.