SQL UNION and UNION ALL operators

SQL UNION operator

The UNION operator is used to combine the result sets of two or more SELECT statements.

Please note that the SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. Additionally, the order of the columns in each SELECT statement must be 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 duplicate values are allowed, please use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Additionally, the column names in the UNION result set are always equal to the column names in the first SELECT statement in UNION.

The original tables used in the following examples:

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

Using UNION command

Example

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, and only one of them is listed. The UNION command will only select 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 1
UNION ALL
SQL Statement 2

Using UNION ALL command

Example:

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