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.