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.

 

sql union and union all

Table of Contents: 

 
  1. Union 
  2. Union All 
  3. Subquery with Union 
  4. Subquery with Union All

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

sql union and union all

Orders table:


Orders Table
order_id(primary key)
order_date
cust_id
prod_id
amount

sql union and union all

Products Table:

Products Table
prod_id(primary key)
prod_name
amount

sql union and union all



Union Example:

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


sql union and union all



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;


sql union and union all


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;


sql union and union all



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;


sql union and union all



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;


sql union and union all



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;


sql union and union all



Post a Comment

Previous Post Next Post