How to Apply UNION and UNION ALL using SQL

August 20, 2021

You can use the following syntax in order to apply UNION and UNION ALL using SQL:

(1) Apply UNION while removing duplicates:

SELECT column_1, column_2,... FROM table_1
UNION
SELECT column_1, column_2,... FROM table_2

(2) Apply UNION ALL while keeping the duplicates:

SELECT column_1, column_2,... FROM table_1
UNION ALL
SELECT column_1, column_2,... FROM table_2

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

Steps to Apply UNION using SQL

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: products_1
  • The name of the second table is: products_2

This is how the products_1 table would look like:

product_name  price
Computer 900
TV 1500
Printer 200
Desk 350

And this is how the products_2 table would look like:

product_name  price
Chair 150
Tablet 400
Printer 200
Monitor 600

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

Step 2: Apply Union using SQL

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

SELECT column_1, column_2,... FROM table_1
UNION
SELECT column_1, column_2,... FROM table_2

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

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

SELECT product_name, price FROM products_1 
UNION
SELECT product_name, price FROM products_2

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

product_name  price
Chair 150
Computer 900
Desk 350
Monitor 600
Printer 200
Tablet 400
TV 1500

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

Step 3 (optional): Apply Conditions under the Union

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

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

SELECT
pr.*
FROM
	(
	SELECT product_name, price FROM products_1 
	UNION
	SELECT product_name, price FROM products_2 
	) pr

WHERE pr.price >= 500

Run the query, and you’ll get:

product_name  price
Computer 900
Monitor 600
TV 1500

Alternatively, you may wish to order the records by the ‘price’ field:

SELECT
pr.*
FROM
	(
	SELECT product_name, price FROM products_1 
	UNION
	SELECT product_name, price FROM products_2 
	) pr

ORDER BY pr.price

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

product_name  price
Chair 150
Printer 200
Desk 350
Tablet 400
Monitor 600
Computer 900
TV 1500

Apply UNION ALL using SQL

You can use the following template to apply UNION ALL:

SELECT column_1, column_2,... FROM table_1
UNION ALL
SELECT column_1, column_2,... FROM table_2

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

Here is the query for our example:

SELECT product_name, price FROM products_1 
UNION ALL
SELECT product_name, price FROM products_2

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

product_name  price
Computer 900
TV 1500
Printer 200
Desk 350
Chair 150
Tablet 400
Printer 200
Monitor 600

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

SELECT DISTINCT
pr.*
FROM
	(
	SELECT product_name, price FROM products_1 
	UNION ALL
	SELECT product_name, price FROM products_2 
	) pr

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

product_name  price
Chair 150
Computer 900
Desk 350
Monitor 600
Printer 200
Tablet 400
TV 1500

You may check the following source for additional SQL tutorials.