Mysql combined query UNION and UNION ALL usage description

1. The functions and syntax of UNION and UNION ALL

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

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

Note: By default, the UNION operator picks different values. If duplicate values are allowed, use UNION ALL.
When ALL is used with UNION 1 (i.e. UNION ALL), duplicate lines are not eliminated
SQL UNION ALL grammar

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2

Note: Also, the column name in the UNION result set is always equal to the column name in the first SELECT statement in UNION.
Note: 1. The column name in the UNION result set is always equal to the column name in the first SELECT statement
2. SELECT statements within UNION must have the same number of columns. The columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same

2. Usage and Precautions of union

union: Union means to combine the results of two or more queries.
Requirement: The number of columns in two queries must be 1
Recommendation: The column type may not be 1, but for each column in the recommended query, the corresponding type should be 1
Data that can come from more than one table: The column name taken from sql statement more than one time may not be the same, in this case the column name of the first sql statement shall prevail.
If the rows retrieved from different statements are exactly the same (the value of each column is the same here), union merges the same rows, leaving only 1 row. As you can see, union removes duplicate lines.
If you don't want to eliminate duplicate lines, use union all.
If the clause has order by,limit, enclose it in brackets (). It is recommended to be placed after all clauses to sort or filter the result of the final merge.
Such as:

(select * from a order by id) union (select * from b order id);

In the clause, 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.

3. Study examples

The original table used in the following example:
Employees_China:

E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming

Employees_USA:

E_ID E_Name
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

Use the UNION command instance

List all the different employee names in China and USA:

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

Results:

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

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

Use the UNION ALL command instance

The UNION ALL and UNION commands are almost equivalent, except that the UNION ALL command lists all the values.

SQL Statement 1
UNION ALL
SQL Statement 2

Example:
List all employees in China and USA:

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

The results of

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

4. Project use examples

The web project often has a full-site search problem, in which a customer wants to type a word into a site's search box and then have it appear in the search results throughout the site. Since one web project cannot be completed in one table, union joint search is generally used to solve the whole problem.

The sql statement of the union joint search used this time is listed below:

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2

0

The joint query of the above SQL statement mainly USES union all and union. The difference between the two is that union all will list all the qualified query results, while union will do 1 to filter all the qualified query results except duplicates.

The interpretation of the SQL statement above is that the article table and web_class table belong to two different tables, so there is no need to remove duplicates here. However, the sql query of the third branch of the above joint query is a query statement composed of participles. The result of this sql query is definitely the result of the sql query of the first branch, so it seems unnecessary here. Therefore, all is not used and duplicate query results are removed.