How to Apply UNION in SQL Server (with examples)

Need to apply a Union in SQL Server?

If so, depending on the scenario, you may use either of the two methods described below:

(1) Apply UNION while removing duplicates:

SELECT Field1,Field2,Field3,... FROM Table1
UNION
SELECT Field1,Field2,Field3,... FROM Table2 

(2) Apply UNION ALL while keeping the duplicates:

SELECT Field1,Field2,Field3,... FROM Table1
UNION ALL
SELECT Field1,Field2,Field3,... FROM Table2 

Next, you’ll see few examples with the steps to apply a Union.

Steps to Apply a UNION in SQL Server

Step 1: Locate the tables

To start, locate the tables that you’d like to union.

For example, let’s union the following two tables, where:

  • The name of the first table is: Clients_1
  • The name of the second table is: Clients_2

This is how the Clients_1 table would look like in SQL Server:

Table with data

And this is how the Clients_2 table would look:

Example of table

Notice how the third record (i.e., the ‘Maria’ record) is identical under both tables. This duplicate will be removed when preforming a UNION. However, it will be kept when performing UNION ALL.

Step 2: Apply the Union in SQL Server

You may use the following template in order to apply a Union in SQL Server:

SELECT Field1,Field2,Field3,... FROM Table1
UNION
SELECT Field1,Field2,Field3,... FROM Table2

Note: When applying a union, you’ll need to make sure that each Select query has the same number of fields.

In the context of our example, the union would look like this:

SELECT First_Name,Last_Name,Age FROM Clients_1 
UNION
SELECT First_Name,Last_Name,Age FROM Clients_2 

Run the query, and you’ll get the following result:

How to Apply UNION in SQL Server

You’ll see that the ‘Maria’ record appears only once (the second duplicate was removed when applying the UNION).

Step 3 (optional): Apply Conditions under the Union

Once you created the Union, you may apply additional conditions.

For example, let’s say that you’d like to get all the records where the Age >= 30. In that case, you may use the query below to get those records:

SELECT
my_clients.*
FROM
(
SELECT First_Name,Last_Name,Age FROM Clients_1 
UNION
SELECT First_Name,Last_Name,Age FROM Clients_2 
) my_clients
WHERE my_clients.Age >= 30

Run the query, and you’ll get:

Query example

Alternatively, you may wish to order the records by the Age field:

SELECT
my_clients.*
FROM
(
SELECT First_Name,Last_Name,Age FROM Clients_1 
UNION
SELECT First_Name,Last_Name,Age FROM Clients_2 
) my_clients
ORDER BY my_clients.Age

You’ll then see the records ordered by the Age (in an ascending order):

Order by

Apply UNION ALL in SQL Server

You can use the following template to apply UNION ALL in SQL Server:

SELECT Field1,Field2,Field3,... FROM Table1
UNION ALL
SELECT Field1,Field2,Field3,... FROM Table2 

With UNION ALL, all the duplicate records will be kept.

Here is the query for our example:

SELECT First_Name,Last_Name,Age FROM Clients_1 
UNION ALL
SELECT First_Name,Last_Name,Age FROM Clients_2 

You’ll now notice that the ‘Maria’ record appears twice:

How to Apply UNION in SQL Server

Note that you’ll get the same results when applying a UNION vs. applying UNION ALL and DISTINCT:

SELECT DISTINCT
my_clients.*
FROM
(
SELECT First_Name,Last_Name,Age FROM Clients_1 
UNION ALL
SELECT First_Name,Last_Name,Age FROM Clients_2 
) my_clients

In that case, the duplicate records will be removed (i.e., the ‘Maria’ record will appear only once):

UNION in SQL Server