The objective of this SQL Server tutorial is to teach you how to use UNION to combine records from two or more tables.
What is UNION in SQL Server?
UNION is an operator which combines the resultset of two or more SELECT queries into a single resultset. It is a set operation and is different from joining two tables with JOIN. The UNION operator combines the rows of the participating tables while JOIN combines the columns of the participating tables on a condition.
Prerequisites: The prerequisite for a successful UNION is that the participating tables should have the same number of columns in the same order and with the same (or similar i.e. the datatype of the common columns should be either exactly same or it should be possible to convert one datatype into another without casting error) datatype.
SQL supports two types of UNIONS – UNION and UNION ALL.UNION – Combines the rows of participating tables with duplicate rows showing only once in the resultset.
UNION ALL – Combines the rows of participating tables with all rows from both tables included in the resultset (which basically means that the duplicate rows are repeated in the resultset).
Operation
The EXISTS or NOT EXISTS operators are used to evaluate subqueries which are part of SELECT, INSERT, UPDATE and DELETE statements. As mentioned above the EXISTS or NOT EXISTS operators do not return any resultset or records but only the Boolean values.
SQL Server UNION Syntax
The basic syntax of the UNION and UNION ALL is given below.
UNION syntax
SELECT_QUERY_1
UNION
SELECT_QUERY_2;
UNION ALL syntax
SELECT_QUERY_1
UNION ALL
SELECT_QUERY_2;
In this syntax,
Select_Query – SQL query which selects rows of information (i.e. records or tuples) from a table.
UNION – SQL keyword to combine the resultset of the participating SELECT statements without repeating the duplicate rows.
UNION ALL – SQL keyword to combine the resultset of the participating SELECT statements showing all rows from both tables including duplicates.
SQL Server UNION Examples
Let us understand both the UNION types with the help of examples. Suppose we have two tables – a table called prospects and another table called customers. The prospects table contains the names of people some of who are already customers and some prospective customers (i.e. who can be converted to customers). The customers table contains the names of people who have been already converted into customers. So, data wise the customers table is a subset of the prospects table. The sample tables are shown below. the prospects table containing 10 records and the customers table containing 8 records.
fname
lname
city
contact
Herbert
Einstein
New York
16117715919
Jackie
Frost
Seattle
16633777771
Stephen
George
Washington
16553120210
James
Mare
New York
16277212992
Angel
Reeves
Dallas
16633233254
Selena
Spears
Detroit
16616165325
Sammy
Louise
Dallas
16110067474
Brandon
Powell
Seattle
16101110778
Kim
Fox
Washington
16529929936
Val
Costner
New York
16991983236
Table: prospects
fname
lname
city
contact
Stephen
George
Washington
16553120210
James
Mare
New York
16277212992
Angela
Crawford
Washington
16633775159
Selena
Spears
Detroit
16616165325
Patty
Campbell
Detroit
16107575525
Brandon
Powell
Seattle
16101110778
Kim
Fox
Washington
16529929936
Edith
Poe
Seattle
16767335231
Table: customers
The common records between the two tables are as below:
Brandon
Powell
James
Mare
Kim
Fox
Selena
Spears
Stephen
George
1) SQL Server UNION – UNION example
First, we will combine the data of the two tables with UNION to get the entire market population. The following statement does so.
SELECT * FROM prospects
UNION
SELECT * FROM customers;
It will generate the following resultset containing details of all the people in both tables but without any record duplication in the resultset.
fname
lname
city
contact
Angel
Reeves
Dallas
16633233254
Angela
Crawford
Washington
16633775159
Brandon
Powell
Seattle
16101110778
Edith
Poe
Seattle
16767335231
Herbert
Einstein
New York
16117715919
Jackie
Frost
Seattle
16633777771
James
Mare
New York
16277212992
Kim
Fox
Washington
16529929936
Patty
Campbell
Detroit
16107575525
Sammy
Louise
Dallas
16110067474
Selena
Spears
Detroit
16616165325
Stephen
George
Washington
16553120210
Val
Costner
New York
16991983236
We can take a count of the above with the following query.
SELECT COUNT(*)
FROM
(SELECT * FROM prospects
UNION
SELECT * FROM customers)
tot_rec;
It will give a count of 13 i.e. 13 records. That is because the two tables have a total of 10+8=18 records out of which 5 are common. Removing 5 from 18 (i.e. 18-5=13) leaves us with 13 records in the UNION resultset.
2) SQL Server UNION – UNION ALL example
Now we will do a UNION ALL on the tables with the following query.
SELECT * FROM prospects
UNION ALL
SELECT * FROM customers;
It will generate the following resultset. We can see that the resultset is larger including all the records from both tables including duplicates.
fname
lname
city
contact
Herbert
Einstien
New York
16117715919
Jackie
Frost
Seattle
16633777771
Stephen
George
Washington
16553120210
James
Mare
New York
16277212992
Angel
Reeves
Dallas
16633233254
Selena
Spears
Detroit
16616165325
Sammy
Louise
Dallas
16110067474
Brandon
Powell
Seattle
16101110778
Kim
Fox
Washington
16529929936
Val
Kostner
New York
16991983236
Stephen
George
Washington
16553120210
James
Mare
New York
16277212992
Angela
Crawford
Washington
16633775159
Selena
Spears
Detroit
16616165325
Patty
Campbell
Detroit
16107575525
Brandon
Powell
Seattle
16101110778
Kim
Fox
Washington
16529929936
Edith
Poe
Seattle
16767335231
We can take a count of the above with the following query.
SELECT COUNT(*)
FROM
(SELECT * FROM prospects
UNION ALL
SELECT * FROM customers)
tot_rec;
It will give a count of 18 i.e. 18 records. That is because the two tables have a total of 10+8=18 records and UNION ALL keeps all the records (without discarding any and repeating the duplicates).
3) SQL Server UNION – with condition and specifying columns
In this last example, we have fine-tuned the query to make it more specific by specifying the columns we want in the resultset and adding a WHERE condition. The query is below.
SELECT fname, lname
FROM prospects
WHERE city='Washington'
UNION
SELECT fname, lname
FROM customers
WHERE city='Washington';
It will generate the following resultset. If we refer above we will see that Kim and Stephen are duplicates but returned only once since we have used UNION.