Learning Objective
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 |
| 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 |
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.
| fname | lname |
| Angela | Crawford |
| Kim | Fox |
| Stephen | George |