SQL guide-union and Union all

Union

The Union command is used to select information about two tables. Similar to the join command. In any case, when the Union command is used, all selected columns must have the same data type.

Note:Union only selects non-repeated values.

SQL Statement 1 UNION SQL Statement 2

---------------------------------------------

Employees_norway:

Employee_id	E_name
01	Hansen, OLA
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Employees_usa:

Employee_id	E_name
01	Turner, Sally
02	Kent, Clark
03	Svendson, Stephen
04	Scott, Stephen

---------------------------------------------

Example of using the Union command

List all employees with different Norwegian and American names.

SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA

Result

Name
Hansen, OLA
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note:This command cannot be used to list all employees in Norway and the United States. In the above example, we have two employees with the same name, but only one is listed. Union only selects non-repeated values.

---------------------------------------------

Union all

The Union all command is equivalent to the Union command. Except Union all, all values are selected.

SQL Statement 1 UNION ALL SQL Statement 2

---------------------------------------------

Example of using the Union all command

List all employees in Norway and the United States.

SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA

Result

Name
Hansen, OLA
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen