ArticleDirectory
- SQL Union syntax
- SQL Union all syntax
- Employees_china:
- Employees_usa:
- Instance
- Result
- Instance:
- Result
QL Union operator
The Union operator is used to merge the result sets of two or more select statements.
Note that the SELECT statement inside the 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. (The table structure is the same)
SQL Union syntax
Select column_name (s) from table_name1
Union
Select column_name (s) from table_name2
Note:By default, the Union operator selects different values. If repeated values are allowed, use Union all.
SQL Union all syntax
Select column_name (s) from table_name1
Union all
Select column_name (s) from table_name2
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.
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 different employee names in China and the United States:
Select e_name from employees_china
Union
Select e_name from employees_usa
Result
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.
Union all
The Union all command is almost equivalent to the Union command, but the Union all command lists all values.
Use the Union all command instance:
List all employees in China and the United States:
Select e_name from employees_china
Union all
Select e_name from employees_usa
Result
e_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas |
Yang, Ming |
Adams, John |
bush, George |
Carter, Thomas |
gates, Bill |