SQL UNION operator
The UNION operator is used to merge the result sets of two or more SELECT statements.
Note::
1. SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.
2. The column name in the UNION result set is always the same as the column name in the first SELECT statement in the UNION.
SQL UNION syntax (no duplicate values in the result set ):
Copy codeThe Code is as follows:
SELECT s FROM table1
UNION
SELECT s FROM table2
SQL UNION ALL syntax (the result set has repeated values ):
Copy codeThe Code is as follows:
SELECT s FROM table1
UNION ALL
SELECT s FROM table2
Example Table:
Table 1 (Employees_China ):
E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming
Table 2 (Employees_USA ):
E_ID E_Name
01 Adams, John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill
Instance:
About UNION:
Copy codeThe Code is as follows:
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
The result is as follows (removing duplicate data ):
E_Name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas |
Yang, Ming |
Adams, John |
Bush, George |
Gates, Bill |
About union all:
Copy codeThe Code is as follows:
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
The result is as follows (repeated data is retained ):
E_Name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas |
Yang, Ming |
Adams, John |
Bush, George |
Carter, Thomas |
Gates, Bill |