MySQL Federated Query Union and UNION ALL usage Introduction

The function and syntax of union and UNION all

The UNION is used to combine the result set of two or more SELECT statements and eliminate any duplicate rows in the table.
The SELECT statement inside 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 CodeThe code is as follows: SELECT column_name from table1
UNION
SELECT column_name from Table2
Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.
When all is used with the Union (that is, union ALL), duplicate rows are not eliminated
SQL UNION All syntax
Copy CodeThe code is as follows: SELECT column_name from table1
UNION All
SELECT column_name from Table2
Note: In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.
Note: 1, the column name in the UNION result set is always equal to the column name in the first SELECT statement
2. The SELECT statement inside 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. use of Union and matters needing attention

Union: The union means to merge two or more query results together.
Requirements: Two queries must have the same number of columns
Recommendation: The type of column can be different, but the recommended query for each column, want the corresponding type to be the same
Data that can come from multiple tables: The column names taken out by multiple SQL statements can be inconsistent, with the column name of the first SQL statement as a standard.
If the rows taken in different statements are identical (in this case, the values for each column are the same), then union merges the same rows and eventually retains only one row. It is also possible to understand that union removes 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 it in parentheses (). After all clauses are recommended, the result of the final merge is sorted or filtered.
Such as:

Copy CodeThe code is as follows: (SELECT * from a ORDER by ID) union (SELECT * from B order ID);

In clauses, the order by needs to be used with limit to make sense. If not used with limit, it will be removed by the parser optimization analysis.

Iii. Examples of learning

The original table is used in the following example:
Employees_china:

Copy CodeThe code is as follows: e_id E_name
Hua Zhang,
Wei Wang,
Carter, Thomas.
Yang, Ming
Employees_usa:
Copy CodeThe code is as follows: e_id E_name
Adams, John.
The Bush, George.
Carter, Thomas.
Gates, Bill.

Using the UNION command instance

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

Copy CodeThe code is as follows: SELECT E_name from Employees_china
UNION
SELECT E_name from Employees_usa
Results:
Copy CodeThe code is as follows: E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Gates, Bill.
Note: This command cannot list all employees in China and the United States. In the above example, we have two employees of the same name, and only one of them is listed. The UNION command will only pick a different value.

Using the UNION all command instance

The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values.

Copy CodeThe code is as follows: SQL Statement 1
UNION All
SQL Statement 2

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

Copy CodeThe code is as follows: SELECT E_name from Employees_china
UNION All
SELECT E_name from Employees_usa
Results
Copy CodeThe code is 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 whole site search problem, that is, customers want to enter a word in the search box of the site, and then throughout the website as long as the page containing the word will appear in the search results. Because a Web project can't be done with a single table, it's generally a union search to solve the whole problem.

The following is a list of the SQL statements used by the Union federated Search:

Copy CodeThe code is as follows:
SELECT * FROM

(SELECT ' id ', ' subject ' from ' article ' WHERE ' active ' = ' 1 ' and ' subject ' like '% adjustment 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 '% adjustment picture% ' ORDER by ' Class_ Id ' DESC ')

As T2

Union

SELECT * FROM

(SELECT ' id ', ' subject ' from ' article ' WHERE ' active ' = ' 1 ' and (' subject ' like '% adjusted% ' OR ' subject ' like '% picture% ') ORDER by ' Ad D_time ' DESC)

as T3;

The combination of the above SQL statements mainly uses the union all and the Union, as for the difference between the two is that union all enumerates all the criteria of the query results, and the Union will do all the matching query results to remove the duplicate results of the filter.

The explanation for the above SQL statement is that because the article table and the Web_class table belong to two different tables, there is no need to remove duplicate results. However, the SQL query statement of the third branch of the above Federated query is a query statement which is composed by word segmentation, and the result of the SQL statement query is definitely the result of the query of the first branch SQL statement, so it is not necessary to use all to remove the duplicate query results.

If you have questions about this article, please submit to the Exchange community, the vast number of enthusiastic netizens will answer for you!! Click to enter the community

Articles you may be interested in:

• Analysis and optimization of MySQL multi-table combined query efficiency
• A popular explanation of several joint queries on MySQL
• MySQL Federated Query Union and order by simultaneous use of solution to error problem
• Deepening SQL multi-table Differentiated federated query
• MySQL Multi-table union query returns the content implementation code of a table
• SQL language Query Basics: Connection Query Union query code
• An explanation of SQL union query and XML parsing instance
• ---example-------

• SELECT ' Business Date ', ' region ', ' Community ', ' open single sector ', ' Order number ', ' waybill number ', ' Package number ', ' shipping method ', ' freight ', ' bag fee ', ' transit fee ', ' forklift fee ', ' other Fees ', ' collection of payment ', ' discount ', ' Amount total ', ' now Pay ', ' owe ', ' pay ', ' delivery fee ', ' insured fee ', ' notes ', ' operator ', ' Operation Time ', ' creator ', ' Creation time ', ' shipping method name '
  UNION
  SELECT main.*,
  Trans_way.code_name as Trans_way_name
  From Tms.tms_settle_income_day Main
  Left JOIN
  Tms.cip_admin_codes Trans_way on
  trans_way.domain_id = ' Product_type ' and
  Main.trans_way = Trans_way.code_type

MySQL Federated Query Union and UNION ALL usage Introduction