'Union' is an operator that combines the resultset of two or more select statements without displaying duplicate values.
Union
The 'Union 'returns the resultset of two or more select statements from similar columns without repeating any value.
To use the Union operator, the columns returned must be present in all the tables it is being queried from and it must have the same name, datatype, same order.
Same order means that if the column 'City' is the second column in the first union statement, then it also must be the second column in the second
select statement too. The column lengths need not be of the same length.
To obtain even duplicate values you can replace UNION with a UNION ALL operator.
SQL 'Union' Syntax
Select column1,column-2,column-3,....column-n
from table A
UNION
Select column1,column-2,column-3,....column-n
from tableB
SQL 'Union All' Syntax
Select column1,column-2,column-3,....column-n
from table A
UNION ALL
Select column1,column-2,column-3,....column-n
from tableB
The Union Example
The 'Employees' tables contains the following columns and their respective data as shown
below.
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
950.00 |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
800.00 |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
700.00 |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
600.00 |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
500.00 |
| 6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
400.00 |
| 7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
650.00 |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
600.00 |
| 9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
750.00 |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
450.00 |
| 11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
500.00 |
The 'EmployeesCanada' table contains the following columns and their respective data as shown
above.
| 1 |
Stan Lee |
4/23/1955 12:00:00 AM |
1 |
1 |
Ottawa |
950.00 |
| 2 |
Hank Marvin |
6/25/1955 12:00:00 AM |
2 |
2 |
Vancouver |
850.00 |
| 3 |
Marina Ann |
2/27/1979 12:00:00 AM |
3 |
4 |
Edmonton |
600.00 |
| 4 |
Terence Michael |
7/9/1981 12:00:00 AM |
3 |
4 |
Toronto |
650.00 |
| 5 |
Vanessa Sterling |
7/19/1983 12:00:00 AM |
7 |
3 |
Quebec |
550.00 |
| 6 |
Martin Birch |
7/23/1979 12:00:00 AM |
8 |
2 |
Victoria |
450.00 |
| 7 |
James Howlett |
9/14/1985 12:00:00 AM |
4 |
6 |
Calgary |
700.00 |
| 8 |
Bernard Shakey |
12/29/1989 12:00:00 AM |
4 |
2 |
Toronto |
675.00 |
| 9 |
Cherie Ann |
2/22/1991 12:00:00 AM |
9 |
3 |
Toronto |
750.00 |
| 10 |
Sandy Pesavento |
7/30/1980 12:00:00 AM |
8 |
2 |
Quebec |
500.00 |
| 11 |
Melissa Hill |
8/25/1992 12:00:00 AM |
NULL |
NULL |
Edmonton |
500.00 |
SelectEmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,City,Salary
from Employees
Union
Select EmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,City,Salary
from EmployeesCanada
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
950.00 |
| 1 |
Stan Lee |
4/23/1955 12:00:00 AM |
1 |
1 |
Ottawa |
950.00 |
| 2 |
Hank Marvin |
6/25/1955 12:00:00 AM |
2 |
2 |
Vancouver |
850.00 |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
800.00 |
| 3 |
Marina Ann |
2/27/1979 12:00:00 AM |
3 |
4 |
Edmonton |
600.00 |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
700.00 |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
600.00 |
| 4 |
Terence Michael |
7/9/1981 12:00:00 AM |
3 |
4 |
Toronto |
650.00 |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
500.00 |
| 5 |
Vanessa Sterling |
7/19/1983 12:00:00 AM |
7 |
3 |
Quebec |
550.00 |
| 6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
400.00 |
| 6 |
Martin Birch |
7/23/1979 12:00:00 AM |
8 |
2 |
Victoria |
450.00 |
| 7 |
James Howlett |
9/14/1985 12:00:00 AM |
4 |
6 |
Calgary |
700.00 |
| 7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
650.00 |
| 8 |
Bernard Shakey |
12/29/1989 12:00:00 AM |
4 |
2 |
Toronto |
675.00 |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
600.00 |
| 9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
750.00 |
| 9 |
Cherie Ann |
2/22/1991 12:00:00 AM |
9 |
3 |
Toronto |
750.00 |
| 10 |
Sandy Pesavento |
7/30/1980 12:00:00 AM |
8 |
2 |
Quebec |
500.00 |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
450.00 |
| 11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
500.00 |
| 11 |
Melissa Hill |
8/25/1992 12:00:00 AM |
NULL |
NULL |
Edmonton |
500.00 |