Union Operators are used to combine the result of two or more select queries into single result set.
SQL UNION Operator:
SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.
SQL Union Operator Syntax:
SELECT column1,column2 FROM table1
UNION
SELECT column1,column2 FROM table2
|
Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error like this
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
|
SQL UNION Example
Look at the following tables:
"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
Now we want to list all the different employees in Norway
and USA.
We use the following SELECT statement:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
The result-set will look like this:
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 will be listed. The UNION command selects only distinct
values.
SQL UNION ALL Example
Now we want to 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