Union and Union all in SQL statements

Document directory

• Employees_china:
• Employees_usa:
• Instance
• Result
• Instance:
• Result

Union

The Union command is used to select related information from two tables, similar to the join command. However, when the Union command is used, the Data Types of all selected columns should be the same.

NOTE: If Union is used, only different values are selected.

SQL Statement 1UNIONSQL Statement 2

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.

Union all

The Union all command is almost equivalent to the Union command, but the Union all command lists all values.

SQL Statement 1UNION ALLSQL Statement 2

Use the Union all command 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