How to Apply UNION and UNION ALL using SQL

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

Steps to Apply UNION using SQL

Step 1: Get the tables

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

For example, here are two tables that you’ll see how to union:

(1) The products_1 table:

(2) And the products_2 table:

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

Step 2: Apply a Union using SQL

To apply a Union using SQL (make sure that each SELECT query has the same number of columns):

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

For our example:

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 a Union).

Step 3 (optional): Apply Conditions under the Union

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

For example, to get all the records where the price >= 500:

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:

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

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

Check the following source for additional SQL tutorials.