How to Apply UNION and UNION ALL using SQL

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:

And this is how the products_2 table would look like:

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:

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:

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):

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:

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):

You may check the following source for additional SQL tutorials.