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
Union:
In the database system, the query to executing two select statements and then combines the two results set into one and eliminates the duplicate rows by using UNION 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 sql server customer,orders and products.
Here the sample table database to download
Customer Table:
| customer Table |
|---|
| cust_id(primary key) |
| cust_name |
| age |
| city |
Orders table:
| Orders Table |
|---|
| order_id(primary key) |
| order_date |
| cust_id |
| prod_id |
| amount |
Products Table:
| Products Table |
|---|
| prod_id(primary key) |
| prod_name |
| amount |
Union Example:
SELECT prod_id,amount
FROM
orders
UNION
SELECT prod_id,amount
FROM
products
ORDER BY
prod_id;
UNION ALL:
In the union 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;
