Use of Mysql UNION query UNION and UNION ALL

I. Functions and syntax 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 UNION must have the same number of columns and the columns must have similar data types.
At the same time, the column sequence 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 selects different values. If repeated values are allowed, use union all.
When ALL is used together with 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 the same as the column name in the first SELECT statement in the UNION.
Note: 1. The column name in the UNION result set is always the same as the column name in the first SELECT statement.
2. The SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. At the same time, the column sequence in each SELECT statement must be the same

Ii. Usage and precautions of union

Union: union means to combine the results of two or more queries.
Required: the number of columns in the Two Queries must be consistent.
Recommendation: The types of columns can be different, but we recommend that you query each column, and you want the corresponding types to be the same
Data from multiple tables: The names of the columns retrieved from multiple SQL statements may be inconsistent. The column names of the first SQL statement shall prevail.
If the rows extracted from different statements are identical (the values of each column are the same), union merges the same rows and retains only one row. We can also understand that union removes duplicate rows.
If you do not want to remove duplicate rows, you can use union all.
If the clause contains order by and limit, enclose them with parentheses. It is recommended to put all the clauses, that is, sort or filter the final merged results.
For example:
Copy codeThe Code is as follows: (select * from a order by id) union (select * from B order id );

In a clause, order by must be used with limit to make sense. If it is not used with limit, it will be removed when the syntax analyzer optimizes the analysis.

Iii. Learning examples

The original table used in the following example:
Employees_China:
Copy codeThe Code is as follows: E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming
Employees_USA:
Copy codeThe Code is as follows: E_ID E_Name
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

Use the UNION command instance

List all 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
Result:
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 with the same name. Only one of them is listed. The UNION command only selects different values.

Use 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
Result
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. project examples

In web projects, the entire site search problem is often encountered, that is, the customer wants to enter a word in the search box of the website, then, the page containing the word will appear in the search results on the entire website. Since a web project cannot use a single table, union searches are generally used to solve the problem.

The following lists the SQL statements used for this union joint search:

Copy codeThe Code is as follows:
Select * from

(SELECT 'id ', 'subobject' FROM 'Article' WHERE 'active' = '1' AND 'subobject' LIKE '% adjust image % 'ORDER BY' add _ time' DESC)

As t1

Union all

Select * from

(SELECT 'id ', 'class _ name' AS 'subobject' FROM 'web _ class' WHERE 'activity' = '1' AND 'class _ name' LIKE '% adjust image % 'ORDER BY' class _ id' DESC)

As t2

Union

Select * from

(SELECT 'id ', 'subobject' FROM 'Article' WHERE 'active' = '1' AND ('subobject' LIKE '% adjust %' OR 'subobject' LIKE '% image % ') order by 'add _ time' DESC)

As t3;

The preceding SQL statement uses union all and union to perform joint queries. The difference between the two is that union all lists all query results that meet the conditions, union performs filtering on all query results that meet the criteria to remove duplicate results.

The preceding SQL statement is interpreted as the article table and web_class table belong to two different tables, so duplicate results are not removed here. However, the SQL query statement of the third branch of the preceding joint query is a query statement that is then combined by word segmentation, the query result of this SQL statement must include the query result of the first branch SQL statement, which is unnecessary here. Therefore, duplicate query results are not removed if all is not used.