About the SQL UNION operator
The UNION operator is used to merge the result sets of two or more SELECT statements.
Attention:
The SELECT statement within 1.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.
The column names in the 2.UNION result set are always equal to the column names in the first SELECT statement in the UNION.
SQL UNION Syntax (result set does not have duplicate values):
Copy Code code as follows:
SELECT s from table1
UNION
SELECT s from table2
SQL UNION All syntax (result set has duplicate values):
Copy Code code as follows:
SELECT s from table1
UNION All
SELECT s from table2
Example table:
Table I (Employees_china):
e_id E_name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Table II (EMPLOYEES_USA):
e_id E_name
Adams, John
Bush, George
Carter, Thomas
Gates, Bill.
Instance:
About Union:
Copy Code code as follows:
SELECT E_name from Employees_china
UNION
SELECT E_name from Employees_usa
The results are as follows (except for duplicate data):
E_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas. |
Yang, Ming |
Adams, John. |
Bush, George. |
Gates, Bill. |
About UNION ALL:
Copy Code code as follows:
SELECT E_name from Employees_china
UNION All
SELECT E_name from Employees_usa
The results are as follows (and duplicate data is retained):
e_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas |
Yang, Ming |
Adams, John |
Bush, George |
Carter, Thomas |
Gates, Bill |