The Union operator for SQL

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

Note that the SELECT statement inside the UNION must have the same number of columns . The column must also have a similar data type . Also, the order of the columns in each SELECT statement must be the same .

SQL UNION Syntax

SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2

Note : By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.

SQL 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 equal to 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.

Using the UNION command instance

List all the different employee names in China and the United States:

Select E_name from Employees_china UNION select E_name from Employees_usa

Results

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 of the same name, and only one of them is listed. the UNION command will only pick a different value .

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_china UNION ALL select E_name from Employees_usa

Results

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

The Union operator for SQL