SQL UNION and UNION ALL
UNION
The UNION command is used to select related information from two tables, much like the JOIN
command. However, when using the UNION command all selected columns need to be of the same
data type.
Note: With UNION, only distinct values are selected.
SQL Statement 1
UNION
SQL Statement 2
|
Employees_Norway:
|
E_ID
|
E_Name
|
|
01
|
Hansen, Ola
|
|
02
|
Svendson, Tove
|
|
03
|
Svendson, Stephen
|
|
04
|
Pettersen, Kari
|
Employees_USA:
|
E_ID
|
E_Name
|
|
01
|
Turner, Sally
|
|
02
|
Kent, Clark
|
|
03
|
Svendson, Stephen
|
|
04
|
Scott, Stephen
|
Using the UNION Command
Example
List all different employee names in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
|
Result
|
E_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 USA. In the example
above we have two employees with equal names, and only one of them is listed. The UNION
command only selects distinct value
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
SQL Statement 1
UNION ALL
SQL Statement 2
|
Using the UNION ALL Command
Example
List all employees in Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
|
Result
|
E_Name
|
|
Hansen, Ola
|
|
Svendson, Tove
|
|
Svendson, Stephen
|
|
Pettersen, Kari
|
|
Turner, Sally
|
|
Kent, Clark
|
|
Svendson, Stephen
|
|
Scott, Stephen
|