UNION and union all operators, unionall Operators

UNION and union all operators, unionall Operators
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

2. Columns must also have similar data types

3. The column sequence in each SELECT statement must be the same

UNION syntax

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

Note: by default, the UNION operator selects different values. If repeated values are allowed, use union all.

Union all syntax

SELECT column_name(s) FROM table_name1UNION ALLSELECT 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_ChinaUNIONSELECT 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.

The union all command is equivalent to the union all command, but the union all command lists ALL values.
Instance:

List all employees in China and the United States:

SELECT E_Name FROM Employees_ChinaUNION ALLSELECT E_Name FROM Employees_USA

Result

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