SQL Server UNION

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.

fnamelnamecitycontact
HerbertEinsteinNew York16117715919
JackieFrostSeattle16633777771
StephenGeorgeWashington16553120210
JamesMareNew York16277212992
AngelReevesDallas16633233254
SelenaSpearsDetroit16616165325
SammyLouiseDallas16110067474
BrandonPowellSeattle16101110778
KimFoxWashington16529929936
ValCostnerNew York16991983236
Table: prospects
fnamelnamecitycontact
StephenGeorgeWashington16553120210
JamesMareNew York16277212992
AngelaCrawfordWashington16633775159
SelenaSpearsDetroit16616165325
PattyCampbellDetroit16107575525
BrandonPowellSeattle16101110778
KimFoxWashington16529929936
EdithPoeSeattle16767335231
Table: customers

The common records between the two tables are as below:

BrandonPowell
JamesMare
KimFox
SelenaSpears
StephenGeorge

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.

fnamelnamecitycontact
AngelReevesDallas16633233254
AngelaCrawfordWashington16633775159
BrandonPowellSeattle16101110778
EdithPoeSeattle16767335231
HerbertEinsteinNew York16117715919
JackieFrostSeattle16633777771
JamesMareNew York16277212992
KimFoxWashington16529929936
PattyCampbellDetroit16107575525
SammyLouiseDallas16110067474
SelenaSpearsDetroit16616165325
StephenGeorgeWashington16553120210
ValCostnerNew York16991983236

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.

fnamelnamecitycontact
HerbertEinstienNew York16117715919
JackieFrostSeattle16633777771
StephenGeorgeWashington16553120210
JamesMareNew York16277212992
AngelReevesDallas16633233254
SelenaSpearsDetroit16616165325
SammyLouiseDallas16110067474
BrandonPowellSeattle16101110778
KimFoxWashington16529929936
ValKostnerNew York16991983236
StephenGeorgeWashington16553120210
JamesMareNew York16277212992
AngelaCrawfordWashington16633775159
SelenaSpearsDetroit16616165325
PattyCampbellDetroit16107575525
BrandonPowellSeattle16101110778
KimFoxWashington16529929936
EdithPoeSeattle16767335231

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.

fnamelname
AngelaCrawford
KimFox
StephenGeorge

Advertisement