SQL Union

3 min readJan 23, 2022

In this tutorial, we are going to learn how to use the SQL UNION operator. An SQL union operator is used to combine two or more result sets from multiple queries.

Table of Contents:

Union syntax:

SELECT column1,column2,column3 
FROM table_name 1 
UNION 
SELECT column4,column5 
FROM table_name 2;

Union All syntax:

SELECT column1,column2,column3 
FROM table_name 1 
UNION ALL 
SELECT column4,column5 
FROM table_name 2;

Use of the SELECT statements and the keyword join them by UNION OR UNION ALL

In the database system, the query to executing two statements and then combines the two results set into one and eliminates the duplicate rows by using keywords. UNION Keywords are sorting the combined result from the table and set by every column matching the rows and eliminating it.

Here ,I just created tables in customer,orders and products.

Customer Table:

Orders table:

Products Table:

SELECT prod_id,amount 
FROM 
orders 
UNION 
SELECT 
prod_id,amount 
FROM 
products 
ORDER BY 
prod_id;

UNION ALL:

In the of all statements the eliminated rows retain in the results table.

SELECT prod_id,amount 
FROM 
orders 
UNION ALL 
SELECT prod_id,amount 
FROM 
products 
ORDER BY prod_id;

In some subquery examples, how to use Union operator and union All operator in Joins such as Left join and Right Join.

Left to Right Join Union:

SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
LEFT JOIN 
customers as c on 
c.cust_id = o.cust_id 
UNION 
SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
RIGHT JOIN 
customers as c on 
c.cust_id = o.cust_id;

Left to Left Join Union:

SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
LEFT JOIN 
customers as c on 
c.cust_id = o.cust_id 
UNION 
SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM orders as o 
LEFT JOIN 
customers as c on c.cust_id = o.cust_id;

Left to Right Union All:

SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
LEFT JOIN 
customers as c on 
c.cust_id = o.cust_id 
UNION ALL 
SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
RIGHT JOIN 
customers as c on 
c.cust_id = o.cust_id;

Left to Left Union All:

SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM 
orders as o 
LEFT JOIN 
customers as c on 
c.cust_id = o.cust_id 
UNION ALL 
SELECT 
c.cust_id,c.cust_name,o.order_date,o.amount 
FROM orders as o 
LEFT JOIN 
customers as c on 
c.cust_id = o.cust_id;